CODEWITHSUNDEEP
sqlsql-server
Brad
Brad

Reputation: 1480

Ordering column in specific order

I have a select statement:

 SELECT DISTINCT LOCATIONTYPE 
 FROM DLOCATION 
 WHERE BinCheckedOut = 'N' 
   AND submitted = 'Y' 
   AND BINNUM > 0 
   AND DISPOSED = 'N';

which grabs all of the LOCATIONTYPE's for a display list. All of the possible LOCATIONTYPE's are:

LOCATIONTYPE
----------------------------------------------
Forage (Represents bin 0 - 999)
International (Represents bin 1000 - 1999)
LongTermRetains (Represents bin 2000 - 2999)
Monitoring (Represents bin 3000 - 3999)
Projects (Represents bin 4000 - 4999)
RawMaterial (Represents bin 5000 - 5999)

However, I would like them listed in a different order by bin number. Is it possible to do this with an order by binnum without selecting the binnum? Is there a better way to manipulate order by to list them in a specific order as shown below?

RawMaterial
Forage  
International
LongTermRetains
Monitoring
Projects

Upvotes: 0

Views: 83

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I would suggest using GROUP BY and a simpler ORDER BY:

SELECT LOCATIONTYPE
FROM DLOCATION
WHERE BinCheckedOut = 'N' and submitted = 'Y' and
      BINNUM > 0 and DISPOSED = 'N'
GROUP BY LOCATIONTYPE
ORDER BY MIN(BINNUM);

Upvotes: 1

John Bell
John Bell

Reputation: 2350

You can use a CASE statement to do a custom ordered list:

SELECT DISTINCT LOCATIONTYPE FROM DLOCATION WHERE BinCheckedOut = 'N' AND
submitted = 'Y' and BINNUM > 0 and DISPOSED = 'N'
ORDER BY 
    CASE 
        WHEN LOCATIONTYPE = 'rawmaterial' THEN 0 
        WHEN LOCATIONTYPE = 'Forage' THEN 1 
        WHEN LOCATIONTYPE = 'International' THEN 2 
        WHEN LOCATIONTYPE = 'LongTermRetains' THEN 3 
        WHEN LOCATIONTYPE = 'Monitoring' THEN 4 
        WHEN LOCATIONTYPE = 'Projects' THEN 5 
    END ASC

Upvotes: 1

Related Questions