Query for nth lowest sequence in a group

Question

Query for nth lowest sequence in a group

SQL33

SSCrazy

Points: 2044
More actions
November 21, 2007 at 6:50 pm

#184328

I want to query the data shown below in the first dataset to return each entire row containing the nth lowest sequence number for each cuslink (see example return in second table below). I've tried the TOP and MAX commands, but they don't seem able (or at least I don't understand enough about them) to get the desired result set.
Thanks in advance for your assistance!!
cuslinksequence
22331
22332
22333
33339
333310
333311
543221
543222
543223
54323
67884
67885
67886
Desire to return this information:
cuslinksequence
22332
333310
543221
67885
Thanks all.. and happy Turkey day!!

Viewing 15 posts - 1 through 15 (of 30 total)

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

DaveItz SSCrazy Points: 2146 More actions · Answer 1

There may well be a better way but I just thought I would, finally, get a chance to play around with something I saw, recently, in another post:

set nocount on

create table #source(

[cuslink] int,

[sequence] int

)

insert into #source values (2233, 1)

insert into #source values (2233, 2)

insert into #source values (2233, 3)

insert into #source values (3333, 9)

insert into #source values (3333, 10)

insert into #source values (3333, 11)

insert into #source values (5432, 21)

insert into #source values (5432, 22)

insert into #source values (5432, 23)

insert into #source values (5432, 3)

insert into #source values (6788, 4)

insert into #source values (6788, 5)

insert into #source values (6788, 6)

create table #dest(

[group] int,

[value] int,

[rank] int null

)

insert into #dest([group], [value])

select [cuslink], [sequence] from #source

create clustered index ixSort ON #dest([group], [value])

declare @last_group int, @last_value int, @rank int

select @last_group = null, @last_value = null

update #dest

set @rank = [rank] = case

when (@last_group is null) or (@last_group <> [group]) then 1

when (@last_value <> [value]) then @rank + 1

else @rank

end,

@last_group = [group],

@last_value = [value]

set nocount off

select [group], [value] from #dest where [rank] = 2

drop table #source

drop table #dest

SQL33 SSCrazy Points: 2044 More actions · Answer 2

Thanks for the help. When I run this, I end up with a table with the rank field populated with "NULL" values, but otherwise matching the original table. I must be running something wrong....

Ronald Nijland SSC Enthusiast Points: 157 More actions · Answer 3

Are the sequence numbers always incremented with 1 per cuslink? If so there is an easier way to do this 🙂

I ran my query and found out that one sequence number is not incrementing with one so mine isn't gonna work :(.. Overlooked it sorry

Vladan SSC-Insane Points: 22072 More actions · Answer 4

I'll use the table structure and data from previous post.

If there is a possibility of multiple occurence of the same "sequence" value for one cuslink (shouldn't be, if that really is sequence), you'll probably need to adapt it.

create table #source(

[cuslink] int,

[sequence] int

)

insert into #source values (2233, 1)

insert into #source values (2233, 2)

insert into #source values (2233, 3)

insert into #source values (3333, 9)

insert into #source values (3333, 10)

insert into #source values (3333, 11)

insert into #source values (5432, 21)

insert into #source values (5432, 22)

insert into #source values (5432, 23)

insert into #source values (5432, 3)

insert into #source values (6788, 4)

insert into #source values (6788, 5)

insert into #source values (6788, 6)

/*variable to hold "N" (as for Nth lowest sequence)*/

DECLARE @n INT

SET @n = 2

/*query to select rows from table; if N is greater than count of rows with that particular cuslink, this cuslink is not returned*/

SELECT s.cuslink, s.[sequence]

FROM #source s

JOIN #source s1 ON s1.cuslink = s.cuslink AND s1.[sequence] <= s.[sequence]

GROUP BY s.cuslink, s.[sequence]

HAVING COUNT(*) = @n

ORDER BY s.cuslink

Disclaimer: This may perform poorly on large tables. Test it on your table and amount of data, add index if it is missing.

SQL33 SSCrazy Points: 2044 More actions · Answer 5

Thanks.. this works great, and is way simpler than I was trying to do.

Have a great thanksgiving!!

SQL33 SSCrazy Points: 2044 More actions · Answer 6

SQL33

SSCrazy

Points: 2044

November 22, 2007 at 12:52 pm

#753455

I'm afraid not always.. that would be too easy!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

JOIN #source s1 ON s1.cuslink = s.cuslink AND s1.[sequence] <= s.[sequence]

That'll work just fine... provided that the CusLink groups don't get large... and they don't need to get real large to start causing some real serious performance problems.

http://www.sqlservercentral.com/Forums/Topic359124-338-1.aspx#bm360151

--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)

SQL33 SSCrazy Points: 2044 More actions · Answer 8

I don't see the custlink groups any larger than 10 per at the most out of a table total of 1500.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Then, you're probably going to be ok...

--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)

johnsql-193053 SSCommitted Points: 1657 More actions · Answer 10

Vladan (11/22/2007)
I'll use the table structure and data from previous post.
If there is a possibility of multiple occurence of the same "sequence" value for one cuslink (shouldn't be, if that really is sequence), you'll probably need to adapt it.
create table #source(
[cuslink] int,
[sequence] int
)
insert into #source values (2233, 1)
insert into #source values (2233, 2)
insert into #source values (2233, 3)
insert into #source values (3333, 9)
insert into #source values (3333, 10)
insert into #source values (3333, 11)
insert into #source values (5432, 21)
insert into #source values (5432, 22)
insert into #source values (5432, 23)
insert into #source values (5432, 3)
insert into #source values (6788, 4)
insert into #source values (6788, 5)
insert into #source values (6788, 6)
/*variable to hold "N" (as for Nth lowest sequence)*/
DECLARE @n INT
SET @n = 2
/*query to select rows from table; if N is greater than count of rows with that particular cuslink, this cuslink is not returned*/
SELECT s.cuslink, s.[sequence]
FROM #source s
JOIN #source s1 ON s1.cuslink = s.cuslink AND s1.[sequence] <= s.[sequence]
GROUP BY s.cuslink, s.[sequence]
HAVING COUNT(*) = @n
ORDER BY s.cuslink
Disclaimer: This may perform poorly on large tables. Test it on your table and amount of data, add index if it is missing.

Hi,

That script does not work if I change value of @n to 3. The sequence of cuslink of 5432 is picked as 22 (wrong) instead of 23 (expected).

SQL33 SSCrazy Points: 2044 More actions · Answer 11

Interesting ... I just tested it too, and verified it does mis identify when 3 is the number. Very puzzling.

SQL33 SSCrazy Points: 2044 More actions · Answer 12

Another user on this forum noted (and I verified) that this script does not work if I change value of @n to 3. The sequence of cuslink of 5432 is picked as 22 (wrong) instead of 23 (expected). Puzzling....

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

Actually - it's not incorrect. If you SORT by custlink and sequence, you end up with

5432 3

5432 21

5432 22 --<--here's rank #3 according to integer ordering....

5432 23

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

SQL33 SSCrazy Points: 2044 More actions · Answer 14

That is a BIG IF!!! LOL. But... seriously, it needs to return the sequence number for each customer link within each custlink, without considering the custlink as part of the sequence