November 21, 2007 at 6:50 pm
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!!
cuslink sequence
2233 1
2233 2
2233 3
3333 9
3333 10
3333 11
5432 21
5432 22
5432 23
5432 3
6788 4
6788 5
6788 6
Desire to return this information:
cuslink sequence
2233 2
3333 10
5432 21
6788 5
Thanks all.. and happy Turkey day!!
November 21, 2007 at 9:53 pm
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
November 21, 2007 at 11:07 pm
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....
November 22, 2007 at 1:10 am
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
November 22, 2007 at 5:00 am
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.
November 22, 2007 at 12:47 pm
Thanks.. this works great, and is way simpler than I was trying to do.
Have a great thanksgiving!!
November 22, 2007 at 12:52 pm
I'm afraid not always.. that would be too easy!
November 22, 2007 at 8:18 pm
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://qa.sqlservercentral.com/Forums/Topic359124-338-1.aspx#bm360151
--Jeff Moden
November 22, 2007 at 11:44 pm
I don't see the custlink groups any larger than 10 per at the most out of a table total of 1500.
November 23, 2007 at 4:15 pm
Then, you're probably going to be ok...
--Jeff Moden
December 6, 2007 at 7:43 am
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).
December 6, 2007 at 8:57 am
Interesting ... I just tested it too, and verified it does mis identify when 3 is the number. Very puzzling.
December 6, 2007 at 11:29 am
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....
December 6, 2007 at 12:20 pm
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?
December 6, 2007 at 1:01 pm
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
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply