How do I return rows with a specific value first?

SqlSql Order-By

Sql Problem Overview

I want my query to return the rows of the table where a column contains a specific value first, and then return the rest of the rows alphabetized.

If I have a table something like this example:

 - 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

And using that table I want to my query to return the rows which contain New York first, and then the rest of the rows alphabetized by city. Is this possible to do using only one query?

Sql Solutions

Solution 1 - Sql

On SQL Server, Oracle, DB2, and many other database systems, this is what you can use:

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

Solution 2 - Sql

If your SQL dialect is intelligent enough to treat boolean expressions as having a numeric value, then you can use:

SELECT *
FROM `Users`
ORDER BY (`city` = 'New York') DESC, `city`

Solution 3 - Sql

My answer may be old and not required but someone may need different approach,hence posting it here.

I had same requirement implemented this, worked for me.

Select * from Users
ORDER BY
(CASE WHEN city = 'New York' THEN 0 ELSE 1 END), city
GO

PS

this is for SQL

Categories
Recommended Sql Solutions
Recommended Sql Order-By Solutions

Previous Solution:

How to create a file in Android?

AndroidFile

Next Solution:

Execute another jar in a Java program

JavaJarExecutable Jar

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionPhoexoView Question on Stackoverflow
Solution 1 - SqlRob FarleyView Answer on Stackoverflow
Solution 2 - SqlchaosView Answer on Stackoverflow
Solution 3 - SqlManjuboyzView Answer on Stackoverflow