Convert "top 3" to 3 columns? - 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)

Convert "top 3" to 3 columns?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-08-25 : 19:07:38

I've got a table like this:

i int identity
desc varchar(20)
score int

And I've got another table like this:

dt datetime,top1 int,top2 int, top3 int

I'm looking to periodically get the top 3 "i" values from the first table, sorted descending by score, into the second table. The only way I can think of doing it is:


declare table @t (i int,score int)
declare @iVal1 int,@iVal2 int,@iVal3 int
insert into @t (i,score)
select top 3 i,score from t1 order by score desc

select top 1 @iVal1=i from @t order by score desc
delete from @t where i=@iVal1
select top 1 @iVal2=i from @t order by score desc
delete from @t where i=@iVal2
select top 1 @iVal3=i from @t order by score desc

insert into @t2 (dt,top1,top2,top3) 
VALUES (getdate(),@iVal1,@iVal2,@iVal3)

That works (or least something just like it works; I just typed that in so forgive typos or stupid mistakes). However, it's pretty ugly and relatively slow.

Is there any way to do this in one fell swoop, without the table variable and deletes and stuff?

Thanks
-b

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-26 : 06:13:31

declare @j varchar(30)
set @j=''
select top 3 @j=@j+cast(i as char(10)) from t order by score desc

insert into tt select datetime(),
cast(substring(@j,1,10) as int),
cast(substring(@j,11,10) as int),
cast(substring(@j,21,10) as int)

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-08-26 : 12:24:32

That is exceptionally clever.

Thank you!

-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources