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 |
|
punky
Starting Member
3 Posts |
 Posted - 2009-04-27 : 15:59:42
|
| Hi, I have two tables namely Order and Status with a relationship in between. To get an order with its respective status, I join the tables. Status table contains two columns namely ID (uniqueidentifier - GUID) and status name (varchar(50) - New, Delivered, Out on delivery).I need to sort the joined tables by the status but I always want that 'New' orders are at the top but I cannot use order by. Is there a way to do this at database level?Thank you. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-04-27 : 16:01:36
|
ORDER BY CASE Status WHEN 'New' THEN 0 ELSE 1 ENDWhy do you write you can't use ORDER BY?
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
punky
Starting Member
3 Posts |
Posted - 2009-04-27 : 16:04:39
|
quote:
Originally posted by Peso
ORDER BY CASE Status WHEN 'New' THEN 0 ELSE 1 ENDWhy do you write you can't use ORDER BY?
E 12°55'05.63"N 56°04'39.26"
I thought ORDER BY can only be used for ascending and descending.Thanks for your quick reply. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-04-27 : 16:12:32
|
The suggestion above is ascending.All records having status equal to "New" is treated as value 0. All other status is treated as value 1.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
punky
Starting Member
3 Posts |
Posted - 2009-04-27 : 16:52:20
|
Thanks Peso. It really slipped off my mind using the CASE. In fact my only solution was to create another column and numbering the status. But this prevents from having extra data  |
|
|
|
|
|
|
Sorting table using varchar - no digits involved