August 13, 2009 at 3:54 am
Hi,
Trying to get my head round some results that i would expect to give me the same value but they differ. I need to be 100% that the reults i am displaying to users are what i think they are.
Below are the two queries i'm running in SQL. The first query returns 355 rows and the one under shows 368.
Based on what i understand from the help i have had with the first query i thought it would show me a one row per delegate_name where many duplicate delegate_name's
I added the second distinct query as i beleive that to do the same and would allow me to cross reference the results to be sure but it doesn't.
If someone could explain why this is happening i would be most grateful.
Results for this query are 355 rows
SELECT uniqueid, course_code, instance_code, awarding_body, delegate_name,
course_title, glh, Employer_postcode, edrs_no, recordmanager, delegateid, entityid
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY delegate_name
ORDER BY delegate_name) r, *
FROM wce_course_delegate_link) A
WHERE r = 1 AND edrs_no = '120211637' order by delegate_name desc
Results for this query are 368 rows
select distinct delegate_name from wce_course_delegate_link where edrs_no='120211637' order by delegate_name desc
August 13, 2009 at 4:30 am
Here is an informative article on ROW_NUMBER()
http://qa.sqlservercentral.com/articles/T-SQL/66512/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 13, 2009 at 4:30 am
Your first query has the edrs_no filter in the wrong place.
Try:
SELECT uniqueid, course_code, instance_code, awarding_body, delegate_name,
course_title, glh, Employer_postcode, edrs_no, recordmanager, delegateid, entityid
FROM
(
SELECT uniqueid, course_code, instance_code, awarding_body, delegate_name,
course_title, glh, Employer_postcode, edrs_no, recordmanager, delegateid, entityid
,ROW_NUMBER() OVER (PARTITION BY delegate_name ORDER BY delegate_name) r
FROM wce_course_delegate_link
WHERE edrs_no = '120211637'
) A
WHERE r =1
ORDER BY delegate_name DESC
August 13, 2009 at 4:33 am
Do you have duplicate values of delegate_name?
August 13, 2009 at 4:36 am
A row could be missing from query 1, if the same delegate_name is against more than 1 edrs_no, and the one assigned to row number 1 is not '120211637'
Your sub query (SELECT ROW_NUMBER() OVER (PARTITION BY delegate_name ORDER BY delegate_name) r, *
FROM wce_course_delegate_link)
could return this:-
FRED, '120211637' -assigned row number 1
FRED, '9999999' - assigned row number 2
but it could easily return this:-
FRED, '9999999' - assigned row number 1
FRED, '120211637' -assigned row number 2
In the second case, FRED will be ignored, because row number 1 is for 9999999
August 13, 2009 at 4:37 am
That's it, thanks for all your replies. Ken well spotted i moved the where clause and it gave me thecorrect results. Thanks again
August 13, 2009 at 4:39 am
Thanks Ian, that is very true i need to think about that as it might cause me some issues.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply