CODEWITHSUNDEEP
sqlsql-order-by
RustyHamster
RustyHamster

Reputation: 359

SQL Custom Order By Clause

A quick Question. I have a query that brings back 2 columns 'Description' and 'Amount' In the Description we have 3 outcomes.

'Gold - owned', 'Bronze - no land' and 'Silver - identified / offered'

I would like the result to show in an order of Gold,Silver,Bronze

Order By Asc or Desc does not achieve this. Is there a way to customize a Order by clause?

Any Help on this Would be appreciated thanks Rusty

Upvotes: 4

Views: 7249

Answers (5)

Praveen ND
Praveen ND

Reputation: 560

Use CASE in ORDER BY Clause:

ORDER BY 
CASE Description
  WHEN ='Gold - owned' THEN 1
  WHEN = 'Silver - identified / offered' THEN 2
  WHEN = 'Bronze - no land' THEN 3
ELSE 4 END

Upvotes: 0

Hadi
Hadi

Reputation: 9

ORDER BY

ORDER BY clause is used to sort data in ascending or descing order.

By default it orders by ascending order if the order is not mentioned.

Syntax:

To order by asc/desc order –

SELECT column_name1,column_name2,… column_nameN   FROM table_name WHERE condition ORDER BYcolumn_name [ASC | DESC];

Example : 

SELECT CustomerId , Name FROM customer_details WHERE CustomerId> 0 ORDER BY CustomerId;

http://academy.comingweek.com/sql-orderby/

Upvotes: -1

Mickey
Mickey

Reputation: 21

Use this in your order by Clause

order by case description when 'gold' then 1 when 'silver'then 2 else 3 end asc

Upvotes: 2

valex
valex

Reputation: 24144

Try to use CASE statement:

ORDER BY ( CASE Description
              WHEN 'Gold - owned' THEN 1
              WHEN 'Silver - identified / offered' THEN 2
              WHEN 'Bronze - no land' THEN 3
              ELSE 0
            END)

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270775

Inside of a CASE, you may ascribe a numeric value to each and order those ascending. If you will need to query a large table, consider adding an index on Description to improve sorting performance.

ORDER BY
  CASE 
    WHEN Description = 'Gold - owned' THEN 0
    WHEN Description = 'Silver - identified / offered' THEN 1
    WHEN Description = 'Bronze - no land' THEN 2
    ELSE 99 /* Any other value (which you should not have) sorts after all */
  END ASC  /* And don't forget to be explicit about ASC order though it's the default */

Since this works like a normal column in the ORDER BY, if you needed to then sort by the Amount or other column, it can be appended with a comma.

ORDER BY 
  CASE
    WHEN Description = 'Gold '...
  END ASC,
  Amount DESC,
  AnotherColumn ASC

Upvotes: 7

Related Questions