Use SQL to query in order except first record

Question

I have an idea, for example. I have a table containing the name (Ann, Ben, Chris, Tom, John),
I want to query it using sql from the letter a and then z.

But I have a condition that I want to put John on the first record.

Answer 1

select name
from names
order by
  case when name = 'John' then 0 else 1 end,
  name

Answer 2

  (SELECT * FROM atable WHERE username = 'John')
UNION ALL
  (SELECT * FROM atable WHERE username <> 'John' ORDER BY username)

Or more general:

  (SELECT * FROM atable ORDER BY username DESC LIMIT 1)
UNION ALL
  (SELECT * FROM atable WHERE id NOT IN (
     SELECT id FROM atable ORDER BY username DESC LIMIT 1)
   ORDER BY username)

If you have to avoid combining for any reason, this slow code will also work:

SELECT * FROM atable 
ORDER BY  
  CASE WHEN id IN (SELECT id FROM atable ORDER BY username DESC LIMIT 1) 
  THEN 0 ELSE 1 END
  , username

In SQL Server, the syntax is slightly different, the subquery:

SELECT TOP 1 id FROM atable ORDER BY username DESC   

Answer 3

:

(SELECT Name
FROM Users
WHERE Name = 'John')
UNION ALL
(SELECT *
FROM Users
WHERE Name <> 'John'
ORDER BY Name)

Answer 4

Placing a case statement in an order by clause does not work with select distinct. I find the following more intuitive and works if you also need a separate file. Although it returns an extra column in the result set.

DECLARE @names TABLE
(
  Name varchar(20)
)

INSERT INTO @names 
SELECT 'Tom'

INSERT INTO @names 
SELECT 'John'

INSERT INTO @names 
SELECT 'Chris'

INSERT INTO @names 
SELECT 'Ann'

INSERT INTO @names 
SELECT 'Ben'

select Name, case when Name = 'John' then 1 else 0 end AS IsTopRow
from @names
order by IsTopRow DESC, Name

Results:

Name    IsTopRow
John    1
Ann     0
Ben     0
Chris   0
Tom     0