Solved

SQL Server ROW_NUMBER Query

Posted on 2013-11-20
5
557 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

739 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