How to add different conditional statements in SQL query

Question

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?

This suggests that maybe you should be using NULL instead of 0? — eggyal
– eggyal, Commented Oct 27, 2013 at 0:10

eggyal · Accepted Answer · 2013-10-27 00:02:24Z

1

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.

answered Oct 27, 2013 at 0:02

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Filipe Silva · Accepted Answer · 2013-10-27 11:32:37Z

1

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

edited Oct 27, 2013 at 11:32

answered Oct 26, 2013 at 23:42

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

1 Comment

Filipe Silva Over a year ago

@eggyal. You are right. Instead of deleting my answer, i wrote a way of ordering it the right way using almost the same approach, but i still like your approach better.

Dwayne Towell · Accepted Answer · 2013-10-26 23:40:25Z

0

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

answered Oct 26, 2013 at 23:40

Dwayne Towell

8,6635 gold badges39 silver badges54 bronze badges

Collectives™ on Stack Overflow

How to add different conditional statements in SQL query

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related