How to Keep Certain Records on Top While Ordering Data? – Interview Question of the Week #110

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)