November 6, 2001 at 9:22 am
I have just stumbled upon a (TSQL language supported) solution that I have been in search of for a LONG time and wanted to share.
How many times have you wanted to use ORDER BY ASC on a field that can contain NULL's and did not want the NULL's returned at the top of the recordset?
I decided to try and use a CASE w/in the ORDER BY clause and to my amazement it was supported.
Now you can write code like this :
SELECT * from systypes
ORDER BY CASE WHEN prec IS NULL THEN 2 ELSE 1 END, prec
Anyways, if you guys knew this already, then more power to you...
November 6, 2001 at 9:48 am
I for one haven't seen this before. Previously I'd created a dummy column and used a case statement to populate it in the select, then used ordered by this dummy column. My method worked, but yours is simpler and returns less data.
Paul Ibison
email: Paul.Ibison@btinternet.com
Paul Ibison
Paul.Ibison@replicationanswers.com
November 6, 2001 at 10:56 am
November 6, 2001 at 1:54 pm
You can also swap the order (the literal just has to be the same case as the field).
order by case when @dir = 'up' then 1 else prec end desc, prec asc
You can use the same technique for ommitting fields from the sort.
Unfortunately you can't include the direction inside a case statement (unless it's changed lately).
a common use is to return recs from a event log.
order by case when status = 'failed' then 1 when status = 'warning' then 2 when 'success' then 3 end
Cursors never.
DTS - only when needed and never to control.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy