How to Prioritize Rows with a Specific Value Then Sort Alphabetically in SQL?

Returning Rows with a Specific Value First

Problem:

You wish to retrieve data from a table, prioritizing the rows that contain a specific value in a particular column. The remaining rows should follow in alphabetical order based on another column.

Consider an example table Users with the following structure:

id	name	city
1	George	Seattle
2	Sam	Miami
3	John	New York
4	Amy	New York
5	Eric	Chicago
6	Nick	New York

Solution:

To prioritize rows based on a specific value and alphabetize the remaining rows, use the following query:

ORDER BY CASE WHEN city = 'New York' THEN 1 ELSE 2 END, city

Explanation:

The CASE expression assigns a priority value of 1 to rows where the city column matches the specified value ('New York') and a priority value of 2 to all other rows. This ensures that rows containing 'New York' appear first in the result set.

The second part of the ORDER BY clause, city, further sorts the rows within each priority group alphabetically based on the city column.

This query will return the following results:

id	name	city
3	John	New York
4	Amy	New York
6	Nick	New York
1	George	Seattle
2	Sam	Miami
5	Eric	Chicago

The above is the detailed content of How to Prioritize Rows with a Specific Value Then Sort Alphabetically in SQL?. For more information, please follow other related articles on the PHP Chinese website!