troubleshooting Question

SQL ROW_NUMBER Behavious

Avatar of Karl_mark
Karl_mark asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
6 Comments1 Solution706 ViewsLast Modified:
I'm seeing some strange behaviour with the Row_Number function in SQL Server 2008. Given the following code:

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, Description

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!)
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros