Ranking question

Question

Post reply

Ranking question

steven.heyvaert

SSC Rookie

Points: 41
More actions
February 15, 2011 at 8:58 am

#235133

Suppose I have pairs like:
'A' 1
'A' 2
'A' 3
'B' 4
'B' 5
'B' 6
'B' 7
'A' 8
'A' 9
'C' 10
'D' 11
And I want as outcome:
'A' 1
'B' 4
'A' 8
'C' 10
'D' 11
= First of each starting group.
Can this be solved by using ranking functions in t-sql ? The difficulty lies in the returning 'A's, I guess.
Or will I need a cursor to get to my result?
Thanks in advance

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

You can definitely get that with ranking functions, or with Min/Max, more efficiently than with a cursor.

;

WITH CTE

AS (SELECT *,

ROW_NUMBER() OVER (PARTITION BY A ORDER BY B) AS Row

FROM ( VALUES ( 'A', 1), ( 'A', 2), ( 'B', 3), ( 'B', 4) )

AS X (A, B))

SELECT *

FROM CTE

WHERE Row = 1 ;

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Ken McKelvey SSCoach Points: 18975 More actions · Answer 2

-- *** Test Data ***

CREATE TABLE #t

(

Col1 char(1) NOT NULL

,Col2 int NOT NULL

)

INSERT INTO #t

SELECT 'A', 1

UNION ALL SELECT 'A', 2

UNION ALL SELECT 'A', 3

UNION ALL SELECT 'B', 4

UNION ALL SELECT 'B', 5

UNION ALL SELECT 'B', 6

UNION ALL SELECT 'B', 7

UNION ALL SELECT 'A', 8

UNION ALL SELECT 'A', 9

UNION ALL SELECT 'C', 10

UNION ALL SELECT 'D', 11

-- *** End Test Data ***

;WITH tGrp

AS

(

SELECT Col1, Col2

,Col2 - ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2) AS Grp

FROM #t

)

,GrpResults

AS

(

SELECT Col1, Grp

,MIN(Col2) AS Col2

FROM tGrp

GROUP BY Col1, Grp

)

SELECT Col1, Col2

FROM GrpResults

ORDER BY Col2

To understand the above, look at the results of:

SELECT Col1, Col2

,ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2) Col1Order

,Col2 - ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2) AS Grp

FROM #t

ORDER BY Col2

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 3

GSquared (2/15/2011)
You can definitely get that with ranking functions, or with Min/Max, more efficiently than with a cursor.
;
WITH CTE
AS (SELECT *,
ROW_NUMBER() OVER (PARTITION BY A ORDER BY B) AS Row
FROM ( VALUES ( 'A', 1), ( 'A', 2), ( 'B', 3), ( 'B', 4) )
AS X (A, B))
SELECT *
FROM CTE
WHERE Row = 1 ;

Hi Gus,

Admittedly, I've not tried your code (I'm at work and can't) but that doesn't look like it will find the "A8" row (for this data example).

I do believe that Ken's probably works because of the delta between two differently partitioned row numbers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

steven.heyvaert SSC Rookie Points: 41 More actions · Answer 4

Gsquared, I tried your version, but I did not get the 'A' 8 results.

Thanks a lot, Ken, your version works for me!

This forum is amazing fast to get results.

Thanks guys, for using your brain for solving my problem!

Steven