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.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2002-11-02 : 08:46:17
|
| This problem is something I came accross when trying to solve another problem. But it has stumped me, and I'm wondering if there's a solution.create table test (value varchar (100) ,sortorder INT )Once the table is populated with values, is there a single line SQL command that will assign integers to the column sortorder from 1 to N such that sortorder follows (ORDER BY value DESC)? 1 is first, 2 is 2nd, etc. This would be easy if ORDER BY could be used with UPDATE.One method would be to update all the table contents in a single pass using declare @cint intset @cint = 0UPDATE Mytable SET value = X.value, @cint = sortorder = @cint + 1 FROM (select top 100 percent value from Mytable order by value desc)But the values should stay in their original rows, so this solution is no good.Can an Update be structured to leave value in place and sequence the sortorder column properly??Sam |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-02 : 09:15:47
|
| Um, I'm not sure why you'd want to do this. Why not just use ORDER BY DESC when selecting from that table? Even if you assigned the integers, what good would they do?There is a way to do it, but I just don't see the value in it. |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-11-02 : 09:32:31
|
| I'd ask the same thing, but this feels like I'm playing poker and someone wants to see my cards.I was trying to come up with a solution for another post in the Transact SQL forum. [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21161[/url]That problem has had no replies and can be neatly solved with a sortorder column in table tblleaderboard that could be JOINED with the table tblprizes column 'ID'.Digress to the solution for the other post:-----------------------------------UPDATE tblleaderboard SET prize = tblprizes.prize FROM tblprizes WHERE tblprizes.id = tblleaderboard.sortorder-----------------------------------I got stumped on populating the sortorder column neatly in table tblleaderboard.Sam |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-02 : 10:16:41
|
| If you really can't use a temp table, then use a table variable:DECLARE @tbl TABLE (value varchar(100), sortorder int identity(1,1))INSERT INTO @tbl (value) SELECT value FROM myTable ORDER BY value DESCI admit I thought the join would work too, but it doesn't. :)There is a way of doing it without using identity in any way, but it does not handle duplicate values sequentially and you will end up with gaps and ties in the sortorder column. It's also a correlated aggregated subquery and will probably perform much more slowly than the table variable/temp table with an identity column. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-11-02 : 10:41:02
|
| Thanks Rob. I've never seen a table variable, and I wonder what the tradeoffs are of table variables over a temp table. In this case, I suppose a temp table is probably a better answer.I'll cook something up and post a solution.Sam |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-02 : 11:22:47
|
| The advantage of a table variable is that it is contained entirely in RAM, whereas a temp table will be written to disk. 99.9% of the time they will perform better than a temp table, but there are rare circumstances where they don't. You can't use INSERT...EXEC or SELECT...INTO on them or index a table variable either, so all searches on a table variable are table scans. That's not a big issue if everything's in RAM but once it a while it can be slower than an indexed temp table. The only real negative thing about table variables is that you can only use them in SQL Server 2000. You can get more about them from Books Online.<rant>My biggest gripe about temp tables is that a lot of people absolutely refuse to consider using them (not you personally Sam, just a general consensus) I can't count the number of posts where people say "I gotta do this but I can't/don't-want-to use a temp table", and in every single case a temp table is the best or at least a damn good solution. For some reason the possible performance issues of temp tables get heralded far louder than their benefits. Seriously, I can't understand that reasoning: if they were no good at all, they wouldn't be an available feature!My advice is, if a temp table is appropriate, TRY it first, and IF it DOESN'T perform well, THEN look for another solution. </rant> |
|
|
|
|
|
|
|
|
Without a temp table