CODEWITHSUNDEEP
sql
red23jordan
red23jordan

Reputation: 2891

Use SQL to query in order except the first record

I have an idea, e.g. I have a table containing name(Ann, Ben, Chris, Tom, John),
I want to query it using sql from letter a first, z last.

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

Upvotes: 10

Views: 20841

Answers (4)

Dave
Dave

Reputation: 659

Placing a case statement in the order by clause does not work with select distinct. I find the following more intuitive and works if you also need select distinct.Although it does return 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

Upvotes: 0

Johan
Johan

Reputation: 76537

  (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 the union for some reason, this slower 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 is:

SELECT TOP 1 id FROM atable ORDER BY username DESC

Upvotes: 3

Chris
Chris

Reputation: 439

It is simple:

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

Upvotes: 0

GSerg
GSerg

Reputation: 78134

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

Upvotes: 36

Related Questions