CODEWITHSUNDEEP
mysql
Mik
Mik

Reputation: 1703

MYSQL order by date of a datetime column

I need to sort products by 3 different fields

  1. order by created at but using just the Date not the Time
  2. within every day get the featured == 1 first

So I need to order by every day first and then get the featured before the non featured once.

SELECT * FROM products ORDER BY ...., featured DESC;

As you can see I miss the part with the date and I cannot find a solution for that.

Thanks in advance for any hint.

Upvotes: 0

Views: 457

Answers (1)

juergen d
juergen d

Reputation: 204746

In MySQL you can do

SELECT * FROM products 
ORDER BY date(created_at),
         featured <> 1;

and generally this works for all DB engines

SELECT * FROM products 
ORDER BY date(created_at) ASC,
         case when featured = 1 then 1 else 0 end DESC;

Upvotes: 1

Related Questions