CODEWITHSUNDEEP
sqlsql-server
Brak Danych
Brak Danych

Reputation: 395

SQL order from x value

I have a table like this

id    title    display_order
9     t9       0
1     t1       3
2     t2       1
3     t3       5
4     t4       4
9     t9       0
10    t10      0

What I need is to have results like this

id    title    display_order
2     t2       1
1     t1       3
4     t4       4
3     t3       5
6     t6       0
9     t9       0
10    t10      0

I could do this:

SELECT *
FROM atable
ORDER BY
  display_order = 0,
  display_order

but unfortunelly I cant do this in SQL Sever 2014. Error appear on this line: display_order = 0,. Do you have any tips how can I do that in SQL server?

Upvotes: 2

Views: 103

Answers (4)

K.Suthagar
K.Suthagar

Reputation: 2306

Here we can order this in two way,

  1. Order the records with Zeros and non Zeros
  2. Then Order the non Zero records respect to the Dislpay_Order.

So Use this following Code to Order that

SELECT *
FROM atable
ORDER BY
  CASE WHEN display_order = 0 then 1 else 0 end, display_order;

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

SELECT *
FROM atable
ORDER BY
  case when display_order = 0 then 1 else 0 end,
  display_order,
  id;

Upvotes: 4

DVT
DVT

Reputation: 3127

SELECT *
FROM atable
ORDER BY
  CASE display_order
    WHEN 0 THEN 1
    ELSE 0 END ASC,
  display_order ASC

Upvotes: 1

Alex
Alex

Reputation: 2337

Yes you can! This is one of the features of SQL Server that just a few know about:

order by 
case when display_order = 0 then 1 else 0 end asc,
display_order asc

Upvotes: 1

Related Questions