By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,363 Members | 2,473 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,363 IT Pros & Developers. It's quick & easy.

I guess this is a relatively simple SELECT order by question

P: 31
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
Share this Question
Share on Google+
4 Replies


Delerna
Expert 100+
P: 1,134
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
Expert 2.5K+
P: 2,878
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

P: 31
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
Expert 2.5K+
P: 2,878
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.