Customizing ORDER BY clause

Posted: August 29, 2014 by Virendra Yaduvanshi in Database Administrator
Tags: , , , ,

As we know ORDER BY clause used to sort result as per specified order – where it may be ASC or DESC. Its sort the result set by specified columns. Its all depends on columns data type.

But in practical environment, sometimes we need result set in a specific order, for example some values should be always on top and its does not matter what are these values, but it should be on top of result set. For example here are some indian cities listed in ASC order as

City Name
Ahmadabad
Banglore
Bhopal
Chennai
Gorakhpur
Jaipur
Kolkatta
Lucknow
Mumbai
Nainital
New Delhi
Pune

Now we want New Delhi and Mumbai always on top in List, The syntax for same will be as

SELECT CityName FROM Table1
ORDER BY CASE WHEN CityName =‘New Delhi’ THEN ‘1’
              WHEN CityName ‘Mumbai’ THEN   ‘2’
              ELSE CityName END ASC

CityName
New Delhi
Mumbai
Ahmadabad
Banglore
Bhopal
Chennai
Gorakhpur
Jaipur
Kolkatta
Lucknow
Nainital
Pune

Happy reading!!!

 

Advertisements
Comments
  1. Diwaker Jain says:

    Thanx alot dear sir,
    really u given a very nice concept and also i learned a new things.

    Like

  2. Dhani Aristyawan says:

    Thank you so much sir, for open up my mind, I never realize that trick in my 16 years of sql experience, shame on me

    Like

  3. Valerie says:

    I haven’t been working with databases for quite some time, but your post has renewed my enthusiasm for how much fun they can be. Thank you.

    Like

  4. Godfrey says:

    This is awesome!

    Like

  5. Steven Meyer says:

    One can also use DECODE to translate and group the cities’ names into something else that is then sorted, followed by the city names when the values from the DECODE are the same.

    In this case, a default is provided for the DECODE to sort the rest of the values after the “interesting” ones.

    This puts New Delhi and Chennai into group 1 which will be first, Bangalore and Mumbai into group 2 which will be next, and the rest of the cities into group 3.

    Then the cities are sorted within each group by their names.

    SELECT CityName
    FROM Table1
    ORDER BY DECODE(CityName,
    ‘Chennai’, 1,
    ‘New Delhi’, 1,
    ‘Banglore’, 2,
    ‘Mumbai’, 2,
    3) ASC,
    CityName;

    CITYNAME
    ———
    Chennai
    New Delhi
    Banglore
    Mumbai
    Ahmadabad
    Bhopal
    Gorakhpur
    Jaipur
    Kolkatta
    Lucknow
    Nainital
    Pune

    Multiple DECODE functions can be used, even nested and combined with CASE, making the possibilities as limitless as your imagination.

    Requirements:

    1) Place Chennai and New Delhi into group 1.
    2) Place Banglore and Mumbai into group 2.
    3) For the rest of the cities:
    a) Group those in Rajasthan into group 3.
    b) Group those in Madhya Pradesh into group 4.
    c) Group the rest into group 5.
    4) Within these groups, group them by population, and sort by these groups.
    a) Cities with more than 1M people first.
    b) Cities with more than 100K people second.
    c) All other cities third.
    5) Finally, within all of these group sort by the city name.

    SELECT CityName
    FROM Table1
    ORDER BY DECODE(CityName,
    ‘Chennai’, 1,
    ‘New Delhi’, 1,
    ‘Banglore’, 2,
    ‘Mumbai’, 2,
    DECODE(State,
    ‘Rajasthan’, 3,
    ‘Madhya Pradesh’, 4,
    5)) ASC,
    CASE
    WHEN Population > 1000000 THEN 0
    WHEN Population > 100000 THEN 1
    ELSE 2
    END ASC,
    CityName ASC;

    Liked by 1 person

  6. Jan says:

    Never realized case statement could be used in order by… Is this also true for group by? Very useful, thanks!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s