Question:Â How to Keep Certain Records on Top While Ordering Data?
Let me elaborate this question as it is originally received.
“Hi Pinal,
I just returned from an interview and I faced a very strange question. I was asked following question and I do not know how to answer.
The interviewer has asked that if I can somehow keep one record on the top of the result set when I order the data. How can I do it?
For example, please see following example where I have four cities. What I want is that I want to keep the data of one city on the top and order the rest how the data by city name, how can I do it?”
Here is the image (I have recreated the image) to explain the problem.
This is a very interesting problem and now let us see the solution for the same.
Answer:
In Order by clause you can use a number which will indicate the ordinal position of the column name used in the select statement. For example Order by 2 means order by the second column values specified in the SELECT statement. However it would be different if used in CASE expression.
Let us create this simple table and explore this
CREATE TABLE #cities(city_id INT, city_name VARCHAR(100)) INSERT INTO #cities(city_id,city_name) SELECT 1,'Chennai' UNION ALL SELECT 2,'New Delhi' UNION ALL SELECT 3,'Mumbai' UNION ALL SELECT 4,'Kolkata' GO
Let us retrieve the data as it is from the table.
SELECT * FROM #cities GO
Now let us retrieve the data by adding additional order by clause.
SELECT * FROM #cities ORDER BY city_name GO
You can see that in the result set we have record Mumbai as a third record. Let us see if we can move that particular order on the top of the result set while keeping the result of the city name ordered alphabetically.
Now let us execute the following statement where I have used a case statement in the ORDER BY clause and it will make sure that city name Mumbai stays on the top of the resultset and the rest of the data is ordered by by city name.
SELECT * FROM #cities ORDER BY CASE WHEN city_name = 'Mumbai' THEN 1 ELSE 2 END , 2 GO
Here CASE expression is used in the ORDER BY clause. It will show you “Mumbai” on top, followed by other cities. The CASE expression assigns the literal value 1 or 2 based on the condition. Mumbai will have 1 and others will have 2. The number 2 followed by the CASE expression indicates the usual meaning (Order by the second column). So at the end, Mumbai comes first and the rest of the city in ascending order
So in ORDER BY clause, a number in a CASE expression assigns a literal value and a number without CASE expression indicates the ordinal position of the column name.
You can also do this with the help of UNION but I have found this much simpler option for ordering data.
Reference: Pinal Dave (https://blog.sqlauthority.com)
13 Comments. Leave new
Pinal, How about… Null if city is Mumbai , else City_name . null will always be on top
SELECT *
FROM #cities
ORDER BY
CASE WHEN city_name = ‘Mumbai’
THEN NULL
ELSE city_name END
GO
It is good Idea, but if you want only specific one city at top, but if you want more than one city on top eg. Mumbai, Pune, Kolkata then you need to use number for it like Pinal’s suggestion.
Thanks Sanjay.
Thanks Sir Jee, Really liked it, the method is very useful for me i worked
Thanks Abdul.
Thanx sir u are great
My pleasure @Sandeep.
Thansk Pinal for explaining in much simpler words.
Keep posting.
Nice example…. I have also face same question in interview
This is a great solution but we can avoid 2 order by clause specially in case of huge tables like this. And if we have only a few handful exceptions.
SELECT *
FROM #cities
ORDER BY IIF(city_name = ‘Mumbai’, CHAR(1), city_name)
We can do this using UNION also.
Thanks.
Ofcourse. I would be interested to see which one performs better.
What a great blog!!! Thank you again for great tip! Ruslan Dubas