How to Prioritize Specific Rows in Database Queries?

Returning Rows with a Specific Value First

In certain database queries, it can be desirable to prioritize the display of rows containing a specific value. For example, consider the following table:

Table: Users

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

If we wish to retrieve the rows where the city column contains "New York" first, and then the remaining rows in alphabetical order of the city, we can utilize the following query:

SQL Server, Oracle, and DB2:

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

This query employs a CASE expression to assign a priority of 1 to rows with "New York" as the city value and a priority of 2 otherwise. The ORDER BY clause then uses this priority along with the city column for sorting. Consequently, the rows with "New York" will be returned first, followed by the rest of the rows ordered alphabetically by city.

The above is the detailed content of How to Prioritize Specific Rows in Database Queries?. For more information, please follow other related articles on the PHP Chinese website!