Feb 18, 2017 8:30:28 PM by Pinal Dave
Question: How to Keep Certain Records on Top While Ordering Data?
Let me elaborate this question as it is originally received.
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.
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
Let us retrieve the data as it is from the table.
Now let us retrieve the data by adding additional order by clause.
ORDER BY city_name
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.
CASE WHEN city_name = 'Mumbai'
ELSE 2 END , 2
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 (http://blog.SQLAuthority.com)
Tags: SQL Server
Written by Pinal Dave
Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 1700 articles on the subject on his blog at http://blog.sqlauthority.com. He is a dynamic and proficient Principal Database Architect who specializes in SQL Server Performance Tuning and has 7+ years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is also Regional Mentor for PASS Asia. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.