ROW_NUMBER() - Using it More then Once In A query

Question

ROW_NUMBER() - Using it More then Once In A query

YSLGuru

SSC-Insane

Points: 21661
More actions
April 22, 2010 at 10:55 am

#221755

Let me say that I'm new to the Ranking Functions in T-SQL because our primary application did not support newer (I guess I should say more recent versions of since 2005 is not technically New) versions of SQL Server till recent years and even then with limited functionality.
One of the more troublesome issues that I've managed to fix thanks to new T-SQL features was possible by using the Ranking Function ROW_NUMBER(). I've setup a view to use the RANKING FUNCTION Row_Number() twice. Once as a way to get the oldest entry in a History table and again so as to get the most recent entry. THis history table stores records for multiple entities and so I can't simply say show me the first record/row or the last record/row from the table.
If I want to see the most recent hsotory record for a specific entity then I would pull from the view using this criteria:
WHERE MYVIEW.RowNumberLast = 1
If I want to see the first entry in the History table (or the oldest record for an entity) via this criteria:
WHERE MYVIEW.RowNumberFirst = 1
My question for you guys is what do you think about using Row_Number() more then once in a query for this kind of purpose?
Kindest Regards,
Just say No to Facebook!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Without seeing the actual code, I can only guess it involves a double sort (one ascending, one descending) prior to the sequence project steps. It could get expensive.

Can you post the code so that we can give better advice?

-- Gianluca Sartori

WayneS SSC Guru Points: 95461 More actions · Answer 2

Since you're new to ranking functions, let me direct you to this recent article here on SQL Server Ranking Functions[/url].

There's no reason that you can't have multiple row_number() function calls in the same select. Just have different criteria for partitioning or ordering.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Lowell SSC Guru Points: 323518 More actions · Answer 3

yeah i've used multiples before; i needed both a counter and a partitioning /grouping of the data;

here's a simple but lame example:

select

Row_number() over ( ORDER BY X.CHK_NO)AS RW,

Row_number() over (PARTITION BY X.CHK_NO ORDER BY X.CHK_NO) As RW1,X.*

From (

SELECT 1234 AS chk_no,'medc' AS ben_code UNION

SELECT 1234,'ss' UNION

SELECT 1234,'fica' UNION

SELECT 1234,'hlth' UNION

SELECT 1235,'medc' UNION

SELECT 1235,'ss' UNION

SELECT 1235,'fica' UNION

SELECT 1235,'hlth' UNION

SELECT 1235,'dent' UNION

SELECT 1236,'medc' UNION

SELECT 1236,'ss' UNION

SELECT 1236,'fica' UNION

SELECT 1236,'hlth' UNION

SELECT 1237,'medc' UNION

SELECT 1237,'ss' UNION

SELECT 1237,'fica' UNION

SELECT 1237,'hlth' UNION

SELECT 1237,'dent' UNION

SELECT 1237,'csup') X

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

YSLGuru SSC-Insane Points: 21661 More actions · Answer 4

Thanks everyone for the replies and links/examples; they've definately helped.

Kindest Regards,

Just say No to Facebook!