DTS solution is possible? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DTS solution is possible?

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2007-12-06 : 11:46:54

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 do not want to use cursor or loop. I prefer using DTS.

Thanks in advance for your assistance.

Sample table ([cuslink] int, [sequence] int)


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:
where @nth=2, output should be

cuslink sequence
------- --------
2233	2
3333 	10
5432 	21
6788 	5

where @nth=3, output should be

cuslink     sequence    
----------- ----------- 
2233        3
3333        11
5432        23
6788        6

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-06 : 22:58:19

May try with linked server.

johnsql
Posting Yak Master

161 Posts

Posted - 2007-12-07 : 10:03:18

quote:
Originally posted by rmiao

May try with linked server.

Thank you for your idea. However, I do not know about linked server. Can you please give me details about it?

johnsql

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-07 : 10:53:31

quote:
Originally posted by johnsql

nth lowest sequence number

Your expected output based on the provided data suggests you want the nth highest...

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-07 : 11:00:58

[code]-- Prepare sample data
CREATE TABLE #Source
(
CusLink INT,
Sequence INT,
Ranking INT
)

CREATE CLUSTERED INDEX IX_Source ON #Source (CusLink, Sequence)

INSERT #Source
(
CusLink,
Sequence
)
SELECT 2233, 1 UNION ALL
SELECT 2233, 2 UNION ALL
SELECT 2233, 3 UNION ALL
SELECT 3333, 9 UNION ALL
SELECT 3333, 10 UNION ALL
SELECT 3333, 11 UNION ALL
SELECT 5432, 21 UNION ALL
SELECT 5432, 22 UNION ALL
SELECT 5432, 23 UNION ALL
SELECT 5432, 3 UNION ALL
SELECT 6788, 4 UNION ALL
SELECT 6788, 5 UNION ALL
SELECT 6788, 6

-- Setup ranking
DECLARE @CusLink INT,
@Ranking INT

SELECT @CusLink = MIN(CusLink),
@Ranking = 0
FROM #Source

UPDATE #Source
SET @Ranking = Ranking = CASE WHEN CusLink > @CusLink THEN 1 ELSE @Ranking + 1 END,
@CusLink = CusLink

-- Show the expected output
SELECT CusLink,
Sequence
FROM #Source
WHERE Ranking = 3

-- Clean up
DROP TABLE #Source[/code]

E 12°55'05.25"
N 56°04'39.16"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-12-07 : 11:08:02

select t1.*
from tbl t1
join
(select Cuslink, seq, num = (select count(*) from tbl t2 where t2.Cuslink = t1.Cuslink and t2.seq <= t1.seq) from tbl t1) t2
on t2.Cuslink = t1.Cuslink
and t2.seq = t1.seq
and t2.num = @nth

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources