How do I return rows with a specific value first?

Question

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?

Consider changing the accepted answer to the second one if you can, as the first one only works for MySQL and not for MSSQL. — Magisch, Mar 06 '17 at 08:23
Done. My original question was about MySQL, but the tags did not reflect that anyway. — Phoexo, Mar 06 '17 at 15:25
Thank you. I came across this this morning in an effort to do something similar for a problem I had. :) — Magisch, Mar 06 '17 at 16:29

Rob Farley · Accepted Answer · 2016-08-09T21:25:32.767

215

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

edited Aug 09 '16 at 21:25

answered Aug 09 '09 at 01:57

Rob Farley

15,625
5
44
58

That actually works in any SQL database (and is a much cleaner solution than the accepted answer in my opinion) – Sep 24 '13 at 20:49
2

Works for me in Oracle. – MonkeyWithDarts Nov 25 '13 at 23:20
How can I add `name` column after `city` ? – stack Nov 22 '15 at 16:27
Put `, name` at the end. – Rob Farley Nov 22 '15 at 19:30
1

And for what I needed to place 'New York' (or another value) last, simply swap the 1 and 2... ORDER BY CASE WHEN city = 'New York' THEN 2 ELSE 1 END, city – deebs Jun 02 '16 at 13:44
Works for me in MSSQL ! – denis Aug 09 '16 at 17:58
This syntax is more compact, but not available in all products & versions. ORDER BY IIF(City = 'New York', 1, 2), City – ooutwire Oct 27 '16 at 17:09
Works in Postgres too. – Sumesh Jan 17 '17 at 04:27
4

The `ELSE 2` section means that while New York gets the value 1, all the other values get the value 2. ...at least as far as the sort order is concerned. – Rob Farley Mar 06 '17 at 09:20

chaos · Answer 2 · 2015-12-10T16:15:37.190

107

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`

edited Dec 10 '15 at 16:15

answered Aug 08 '09 at 23:41

chaos

122,029
33
303
309

1

@MehrdadAfshari: No, MSSQL is too stupid to process equivalency tests in the `ORDER BY` clause. – chaos Sep 11 '13 at 15:13
@a_horse_with_no_name: Have you tried running the above query against MSSQL? – chaos Sep 25 '13 at 15:01
11

@chaos: the above statement does indeed *not* run with SQL Server, but that's because the syntax is non-standard and only works for MySQL. When using a `CASE` statement (which is standard SQL) SQL Server can very well use an expression in the `ORDER BY` clause. "*too stupid to process equivalency tests*" is simply wrong. If at all it should read: "*does not support MySQL's implicit casting of boolean `true` to the value `1` (one).*" – Sep 25 '13 at 15:02
@Esraa_92: Then you don't have a SQL dialect that can handle it and you need Rob Farley's answer. – chaos May 13 '16 at 19:08
6

For Postgres, this worked for me: `ORDER BY id = 123 DESC, name ASC` – user1032752 Oct 06 '16 at 21:27

score 6 · Answer 3 · answered Nov 27 '17 at 04:19

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

How do I return rows with a specific value first?

3 Answers3

Linked

Related