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 |
|
godspeedba
Yak Posting Veteran
90 Posts |
 Posted - 2010-01-04 : 09:51:41
|
| Dear friendsHope you are all well!I am trying to resolve an order by issue and would appreciate some assistance with.I have a column(called random) that is full of random numbers which requires to be sorted. (that can't be the problem that's too easy I hear you ask)However this needs to be sorted in addition with another column that contains order numbers(called order). Most of these however do not contain order numbers.What I am trying to do is sort the data by the random number that has a number in the order field before eventually filtering down to records that has no order numbers.so the result table would look a like the belowrandom order932333 2313232332 2331123123 3233999322733232544324432131323133Any ideas please? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-04 : 10:27:31
|
Just an idea:order by random DESC, case when isnull(order,'')='' then 9999 else order end
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
godspeedba
Yak Posting Veteran
90 Posts |
Posted - 2010-01-04 : 10:30:08
|
| That would only sort by the random field, my understanding is that it would only do the second sort by if, the database contained two random numbers that were the same. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-04 : 11:13:43
|
You are right, sorry.This should work:order by case when [order] is null then random else 1000000+randomendDESC
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
ORDER BY