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

Interview question: A reader was asked to put one city first while keeping the other cities in alphabetical order. Can ORDER BY do both?

Answer: Yes. Sort first on a CASE expression that gives Mumbai priority, then sort on the city name.

The reader sent me four cities after an interview. Mumbai was third when the names were sorted alphabetically, but the interviewer wanted it at the top. I recreated the little result set because seeing the three orders makes the answer easier to remember.

-- Use a fresh query window. Do not overwrite an existing demonstration table.
IF OBJECT_ID(N'tempdb..#cities') IS NOT NULL
    THROW 50112, '#cities already exists in this session. Open a fresh query window.', 1;

CREATE TABLE #cities
(
    city_id int NOT NULL PRIMARY KEY,
    city_name varchar(100) NOT NULL
);
INSERT #cities (city_id, city_name)
VALUES (1, 'Chennai'), (2, 'New Delhi'),
       (3, 'Mumbai'), (4, 'Kolkata');

SELECT city_id, city_name FROM #cities;

This is the original unsorted result captured for the example. Without ORDER BY, SQL Server does not promise this row order.

SELECT city_id, city_name
FROM #cities
ORDER BY city_name, city_id;

Alphabetical order puts Mumbai after Kolkata:

SELECT city_id, city_name
FROM #cities
ORDER BY CASE WHEN city_name = 'Mumbai' THEN 0 ELSE 1 END,
         city_name,
         city_id;

Now Mumbai comes first. The other cities remain alphabetical. The last column is a stable tie breaker if two rows have the same city name.

In my original answer, ORDER BY 2 referred to the second selected column, but that shortcut makes the sort depend on the select-list position. Writing city_name makes the rule easier to read and maintain. The 0 and 1 inside CASE are priority values, not column positions.

DROP TABLE #cities;

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.