Karl_mark
asked on
SQL ROW_NUMBER Behavious
I'm seeing some strange behaviour with the Row_Number function in SQL Server 2008. Given the following code:
Most of the numbers in the first column are correct, but at some points the row number does not return to 1 when the partition is applied. For example:
1 812 4278 1:BLA:D1 NFG
2 813 4278 1:BLA:D1 NFG
3 814 4278 1:BLA:D1 NFG
4 815 4278 1:BLA:D1 NFG
5 816 4278 1:BLA:D1 NFG
4 817 4314 1:BLA:D1 NFG
5 818 4314 1:BLA:D1 NFG
6 819 4314 1:BLA:D1 NFG
As you can seem the third column (Identifier) changes from 4278 to 4314, but the Row_Number starts from 4. Any ideas why this would happen? I've used Row_Number on many occasions before without any problems, although this is the first time that I've used two in one query (Bad database design; there is no unique identifier column in the database tables, also why there are reserved keywords as column names!)
SELECT ROW_NUMBER() OVER(PARTITION BY ACPL.Identifier ORDER BY ACPL.ClassName,
ACPL.Identifier),
ROW_NUMBER() OVER(ORDER BY ACPL.ClassName, ACPL.Identifier),
ACPL.Identifier, ACPL.ClassName, Description,
MAX(UCAS.Initials), ' '
FROM MarkingRecords..vw_KRC_SummerProfileSubmittedACPLNew ACPL
JOIN MarkingRecords..vw_KRC_SummerProfileSubmittedUCAS UCAS
ON ACPL.ClassName = UCAS.ClassName AND
ACPL.Identifier = UCAS.Identifier
GROUP BY ACPL.Identifier, ACPL.ClassName, DescriptionMost of the numbers in the first column are correct, but at some points the row number does not return to 1 when the partition is applied. For example:
1 812 4278 1:BLA:D1 NFG
2 813 4278 1:BLA:D1 NFG
3 814 4278 1:BLA:D1 NFG
4 815 4278 1:BLA:D1 NFG
5 816 4278 1:BLA:D1 NFG
4 817 4314 1:BLA:D1 NFG
5 818 4314 1:BLA:D1 NFG
6 819 4314 1:BLA:D1 NFG
As you can seem the third column (Identifier) changes from 4278 to 4314, but the Row_Number starts from 4. Any ideas why this would happen? I've used Row_Number on many occasions before without any problems, although this is the first time that I've used two in one query (Bad database design; there is no unique identifier column in the database tables, also why there are reserved keywords as column names!)
PortletPaul
It is due the group by I think (some records aren't being returned due to grouping)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the responses.
PortlettPaul, I tried using the query you suggested, but I still get the same results.
awking00, I also tried simplifying the query by removing the join to the UCAS table to see what the results look like without a GROUP BY and a MAX(). Again, still the same results!
I'm wondering if it may be easier to separate the query into two by making either a temp table or using a CTE to create the initial ROW_NUMBER results and then running ROW_NUMBER again to get the record number for each Admission Number against ClassName.
PortlettPaul, I tried using the query you suggested, but I still get the same results.
awking00, I also tried simplifying the query by removing the join to the UCAS table to see what the results look like without a GROUP BY and a MAX(). Again, still the same results!
I'm wondering if it may be easier to separate the query into two by making either a temp table or using a CTE to create the initial ROW_NUMBER results and then running ROW_NUMBER again to get the record number for each Admission Number against ClassName.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Actual solution figured out by breaking down the code using suggestions from others.