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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-08-23 : 09:59:53
|
| Mike writes "Let's say you have a 2 column table (Name - varchar, Order - Number). You have 25 records in the table and no values in the 'Order' column. Create a select statement that will sort the records by name (Asc) and will update the 'Order' field with the value of the sequence. (Ex. When sorted, the record with the name 'Davis' turns out to be the 4th record. The 'Order' field should be updated with the number 4) I have been trying to figure out a way to do this. Can this be done inone select statement? If so, how?" |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-08-23 : 10:07:27
|
| Maybe something like ... assuming Name is a unique column ... CREATE TABLE #temp (Name VARCHAR(255), Order INT IDENTITY)INSERT INTO #temp (Name) SELECT Name FROM SortedList ORDER BY NameUPDATE sl SET sl.Order = t.Order FROM SortedList AS sl INNER JOIN #temp AS t ON sl.Name = t.Nameif Name is not unique maybeALTER TABLE SortedList ADD COLUMN NewOrder INT IDENTITYthen update the tableUPDATE SortedList SET Order = NewOrderthen remove the NewOrder columnALTER TABLE SortedList DROP COLUMN NewOrder... hope i helped |
 |
|
|
|
|
|
Select Statement