I guess this is a relatively simple SELECT order by question

[Cyprus106]

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.

[Delerna]

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

SELECT * 
FROM BKMASTER 
WHERE MATKEY='MCCOECO' 
ORDER BY isnull(MEDSTA,'zzzzzz') ASC
 

[ck9663]

try this:

SELECT * FROM BKMASTER 
WHERE MATKEY='MCCOECO' 
ORDER BY 
case 
   when MEDSTA  is null then space(5) 
   when MEDSTA = 'RT' then '0' + MEDSTA 
   else  '1' + MEDSTA end ASC
 

-- CK

[Cyprus106]

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 (

[ck9663]

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?

SELECT * FROM BKMASTER 
WHERE MATKEY='MCCOECO' 
ORDER BY 
case 
   when MEDSTA  is null then space(5) 
   when MEDSTA = 'RT' then '0' + MEDSTA 
   when MEDSTA = 'OE' then '1' + MEDSTA 
   else  '2' + MEDSTA end ASC