CODEWITHSUNDEEP
mysql
M Fayyaz
M Fayyaz

Reputation: 165

How to add different conditional statements in SQL query

I have a column name 'Code' in my database and it contain different numbers like this..

Product      |      Code
===============================
  p1         |       0
  p2         |      120
  p3         |       0
  p4         |      222
  p5         |       1
  p6         |       1
  p7         |      180
  p18        |      300

I want to sort table, order by Code. Products with code '1' comes first, then all the numbers in ASC order, and at the end show Products with code '0'. Like This

Product      |      Code
===============================
  p5         |       1
  p6         |       1
  p2         |      120
  p7         |      180
  p4         |      222
  p18        |      300
  p3         |       0
  p1         |       0

Any help for SQL query Thanks?

Upvotes: 1

Views: 64

Answers (3)

Filipe Silva
Filipe Silva

Reputation: 21657

You can do that with a trick with UNION ALL:

SELECT Product,Code FROM (
   SELECT Product,Code,0 AS ORD FROM Table1 WHERE code != 0
UNION ALL
    SELECT Product,Code,1 FROM Table1 WHERE code=0
) a
ORDER BY ORD,CODE

This will give you The != 0 first, ordered by code and then the =0.

sqlfiddle demo

Upvotes: 1

eggyal
eggyal

Reputation: 125865

First sort on whether Code = 0 (in ascending order, which is the default, false/0 comes before true/1); then sort on the value of Code itself:

SELECT * FROM my_table ORDER BY Code = 0, Code

See it on sqlfiddle.

Upvotes: 1

Dwayne Towell
Dwayne Towell

Reputation: 8583

SELECT product,code FROM table ORDER BY CASE WHEN Code=0 THEN (SELECT max(Code)+1 FROM table) ELSE Code END ASC;

Upvotes: 0

Related Questions