Solved

SQL Server ROW_NUMBER Query

Posted on 2013-11-20
5
546 Views
Last Modified: 2013-11-20
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
Comment
Question by:Karl_mark
  • 2
5 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39662521
>ORDER BY LearnerRef ASC
>I'd expect this to rank ... from high to low
Change ASC to DESC in your T-SQL
0
 
LVL 9

Expert Comment

by:COANetwork
ID: 39662538
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
 

Accepted Solution

by:
Karl_mark earned 0 total points
ID: 39662540
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
 

Assisted Solution

by:Karl_mark
Karl_mark earned 0 total points
ID: 39662541
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now