• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 577
  • Last Modified:

SQL Server ROW_NUMBER Query

Hi,
I use the ROW_NUMBER function fairly regularly and can normally get it to do what I'm achieving. However, I'm getting some odd behaviour which I can't figure out. I'm running the following query:

	
	SELECT 
		LearnerRef, 
		ROW_NUMBER() OVER(PARTITION BY LearnerRef ORDER BY LearnerRef ASC, GuidedLearnHours DESC),
		AimRef, 
		GuidedLearnHours
		--INTO #TempStudentGLHTable
	FROM 
		LearnerDataAimsTable
	WHERE 
		AimRef NOT LIKE 'CMISC%' AND
		ActualEndDate IS NULL
	ORDER BY
		LearnerDataAimsTable.LearnerRef ASC,
		LearnerDataAimsTable.GuidedLearnHours DESC

Open in new window


I'd expect this to rank the GuidedLearnHours for each learner from high to low, but instead I get this:

LearnerRef      (No column name)      AimRef      GuidedLearnHours
10                           1                              50096370      41  
10                           2                              50067552      328  
10                           3                              50022660      164  
10                           4                              50025946      164  

I've tried various combinations of ORDER BY, both within the function and the query and neither seems to achieve what I want (my understanding is that the ROW_NUMBER function effectively runs as a sub-query anyway so it overrides the main query ranking for the purpose of assigning a row number).
0
Karl_mark
Asked:
Karl_mark
  • 2
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>ORDER BY LearnerRef ASC
>I'd expect this to rank ... from high to low
Change ASC to DESC in your T-SQL
0
 
COANetworkCommented:
Remove the ASC order by LearnerRef from inside the function
SELECT 
		LearnerRef, 
		ROW_NUMBER() OVER(PARTITION BY LearnerRef ORDER BY GuidedLearnHours DESC),
		AimRef, 
		GuidedLearnHours
		--INTO #TempStudentGLHTable
	FROM 
		LearnerDataAimsTable
	WHERE 
		AimRef NOT LIKE 'CMISC%' AND
		ActualEndDate IS NULL
	ORDER BY
		LearnerDataAimsTable.LearnerRef ASC,
		LearnerDataAimsTable.GuidedLearnHours DESC

Open in new window

0
 
Karl_markAuthor Commented:
Hi Jim,
Sorry, I figured it out. Stupidly, the source table has the GuidedLearnHours as char(5). I converted it to INT in the ORDER BY Clause within the ROW_NUMBER function and it now works.
It's a legacy table so don't blame me!
0
 
Karl_markAuthor Commented:
So, the working code is:

	
        SELECT 
		LearnerRef, 
		ROW_NUMBER() OVER(PARTITION BY LearnerRef ORDER BY CAST(GuidedLearnHours AS INT) DESC),
		AimRef, 
		GuidedLearnHours
		--INTO #TempStudentGLHTable
	FROM 
		LearnerDataAimsTable
	WHERE 
		AimRef NOT LIKE 'CMISC%' AND
		ActualEndDate IS NULL
	ORDER BY
		LearnerDataAimsTable.LearnerRef ASC,
		LearnerDataAimsTable.GuidedLearnHours DESC

Open in new window

0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now