MySQL Order by Two Columns

Question

I have a Table Like Below


    CREATE TABLE Products(Product_id INT, ProductName VARCHAR(255), 
                          Featured enum('Yes', 'No'), Priority enum('p1', 'p2', 'p3'))


    INSERT INTO Products(ProductName, Featured, Priority) 
                  VALUES('Product A', 'Yes', 'p1'),
                        ('Product B', 'No',  'p2'),
                        ('Product C', 'Yes', 'p1'),
                        ('Product D', 'No',  'p1'),
                        ('Product E', 'Yes', 'p3'),
                        ('Product F', 'No',  'p2'),
                        ('Product G', 'Yes', 'p1'),
                        ('Product H', 'Yes', 'p2'),
                        ('Product I', 'No',  'p2'),
                        ('Product J', 'Yes', 'p3'),
                        ('Product K', 'Yes', 'p1'),
                        ('Product L', 'No',  'p3');

I Need to get the Featured products followed by product with priority p1, p2 and p3


Op:
   ProdName | Featured  | Priority

    Product A   Yes         p1
    Product C   Yes         p1
    Product G   Yes         p1
    Product K   Yes         p1
    Product H   Yes         p2
    Product E   Yes         p3
    Product J   Yes         p3
    Product D   No          p1
    Product B   No          p2
    Product F   No          p2
    Product I   No          p2
    Product L   No          p3

I Wrote a query below which ain't working..

                                           
  SELECT * 
    FROM Products
   ORDER BY Featured IN ('Yes') desc,
            Priority IN ('p1', 'p2', 'p3') desc

Could u plz spot mistake in that

What error do you see? the statement as you wrote it should work, ans so should the simplified one Yogendra Singh recommends. Are you selecting only Product_id column? it will be all NULL's in your example. — Yuri, Oct 30 '12 at 05:43
Possible duplicate of [PHP MySQL Order by Two Columns](http://stackoverflow.com/questions/514943/php-mysql-order-by-two-columns) — Jim Fell, Jun 13 '16 at 19:39

ganesh · Accepted Answer · 2012-10-30T05:48:12.237

16

Try this

Select * from Products ORDER BY Featured, Priority

If you use ORDER BY on mysql enum it will not order it by alphabetically but it will order it by its position in enum.

If you want to order alphabetically as you describe cast the enum name to a string like this

Select * from Products ORDER BY  concat(Featured) desc , Priority

edited Oct 30 '12 at 05:48

answered Oct 30 '12 at 05:39

ganesh

1,006
14
30

Yogendra Singh · Answer 2 · 2012-10-30T12:16:09.610

3

Why don't you simply use SQL as :

 SELECT * 
 FROM Products
  ORDER BY Featured desc,
        Priority asc;

By doing this Yes will appear before No. P1 will appear before P2 and P2 before P3. I believe, that is what you want.

If data type issue in ordering then,

 SELECT * 
 FROM Products
  ORDER BY CONCAT(Featured) desc,
        CONCAT(Priority) asc;

edited Oct 30 '12 at 12:16

answered Oct 30 '12 at 05:35

Yogendra Singh

33,927
6
63
73

swapnesh · Answer 3 · 2012-10-30T05:53:46.083

0

Check this query--

 SELECT * 
 FROM Products
  ORDER BY Featured asc,Priority,ProductName asc;

Fiddle

Working code check fiddle

edited Oct 30 '12 at 05:53

answered Oct 30 '12 at 05:43

swapnesh

26,318
22
94
126

score -1 · Answer 4 · edited Feb 01 '17 at 17:13

-1

SELECT * 
    FROM Products
where Featured IN ('Yes') and
      Priority IN ('p1', 'p2', 'p3')
Order by Featured asc,Priority,ProductName asc;

This should work

edited Feb 01 '17 at 17:13

ρяσѕρєя K

132,198
53
198
213

answered Feb 01 '17 at 16:40

aftab md

1

MySQL Order by Two Columns

4 Answers4

Related