WikiDBA

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;

LikeLiked by 1 person