469,343 Members | 5,377 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,343 developers. It's quick & easy.

I guess this is a relatively simple SELECT order by question

I'm just trying to order some data correctly. In that respect, I've got the following SELECT statement...

SELECT * FROM BKMASTER WHERE MATKEY='MCCOECO' ORDER BY MEDSTA ASC

The thing is, there are only about 5 two-digit options for MEDSTA: NULL, "OE" "OP" "RT", etc. I want any records with blank MEDSTAs to come first, then records where MEDSTA='RT' to come second, then whatever else. I've tried a few different things, but who am I fooling? I don't have a clue how to do this!

Much appreciated.
Apr 11 '08 #1
4 971
Delerna
1,134 Expert 1GB
So you need to sort in descending order but thye problem with that is the null's come at the end and you want them at the top.

Try this
Expand|Select|Wrap|Line Numbers
  1. SELECT * 
  2. FROM BKMASTER 
  3. WHERE MATKEY='MCCOECO' 
  4. ORDER BY isnull(MEDSTA,'zzzzzz') ASC
  5.  
Apr 11 '08 #2
ck9663
2,878 Expert 2GB
try this:

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM BKMASTER 
  2. WHERE MATKEY='MCCOECO' 
  3. ORDER BY 
  4. case 
  5.    when MEDSTA  is null then space(5) 
  6.    when MEDSTA = 'RT' then '0' + MEDSTA 
  7.    else  '1' + MEDSTA end ASC
  8.  
-- CK
Apr 12 '08 #3
delerna,

You that's right, but see I need to order them by NULL first, then by which ones say "RT" after than, then by "OE", then whatever's left...

I tried your code (although like I said I only know the real basics so I wasn't entirely sure what to put in place of the ZZZZs) and it threw an "unknown token" code right after isnull. It didn't recognize the (
Apr 12 '08 #4
ck9663
2,878 Expert 2GB
delerna,

You that's right, but see I need to order them by NULL first, then by which ones say "RT" after than, then by "OE", then whatever's left...

I tried your code (although like I said I only know the real basics so I wasn't entirely sure what to put in place of the ZZZZs) and it threw an "unknown token" code right after isnull. It didn't recognize the (
You have to sure which one would you want as top 3.

Null, RT and OE then the rest?
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM BKMASTER 
  2. WHERE MATKEY='MCCOECO' 
  3. ORDER BY 
  4. case 
  5.    when MEDSTA  is null then space(5) 
  6.    when MEDSTA = 'RT' then '0' + MEDSTA 
  7.    when MEDSTA = 'OE' then '1' + MEDSTA 
  8.    else  '2' + MEDSTA end ASC
Apr 12 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

13 posts views Thread by LRW | last post: by
5 posts views Thread by Tim::.. | last post: by
7 posts views Thread by Scott Frankel | last post: by
20 posts views Thread by Brian Tkatch | last post: by
24 posts views Thread by firstcustomer | last post: by
1 post views Thread by Ganesh Muthuvelu | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.