CODEWITHSUNDEEP
mysqlsql
Code Poet
Code Poet

Reputation: 11437

order a result set in unnatural order

I understand the normal ASC and DESC ORDER BY clause.

However, I have a case where table contains a column unitType where unitType can be 0, 1 or 2. I need to sort my result set so that the rows returned are in a particular order depending the value of the unitType column.

This is the closest I have gotten:

SELECT * FROM `units` WHERE `unitType`=0
union 
select * from units where unitType=2
union 
select * from units where unitType=1

This lists my rows with unitType=0, followed by those with value 2 and finally 1. Is there a better way to do this? I need to alter this query to fetch rows in any particular order e.g. 2, 0, 1 etc.

Upvotes: 3

Views: 165

Answers (5)

onedaywhen
onedaywhen

Reputation: 57023

Rather than 'hiding' this logic in the ORDER BY clause of a query, consider creating a table to map unitType and sort order values (e.g. a two-column base table), join to this table in queries and include the sort order attribute in the SELECT clause and the ORDER BY clause (to expose the sort order to the calling application).

Upvotes: 1

The Ed R
The Ed R

Reputation: 326

I'd create a seperate SortOrder table (UnitType,Ordinal) and use that to define ordering. You'd then use UnitType to join to that table and order results by Ordinal.

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

You can also use the FIELD() function:

SELECT * 
FROM units 
ORDER BY FIELD(unitType, 0,2,1)

Upvotes: 3

aF.
aF.

Reputation: 66697

select * from units
order by
  case when unitType = 0 then 1
       when unitType = 1 then 3
       else 2 end

Upvotes: 3

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

SELECT *
    FROM units
    WHERE unitType IN (0,1,2)
    ORDER BY CASE WHEN unitType = 0 THEN 1
                  WHEN unitType = 2 THEN 2
                  WHEN unitType = 1 THEN 3
             END

Upvotes: 7

Related Questions