I want to output all entries of today, sorted by the most popular entries (most likes = most popular).
I'm using this query which just selects the most popular entries in the past 24 hours, but if it's 0:00 o'clock for example the count should reset to zero. I also want to order alphabetically if entries have the same count-amount.
How could I achieve that? I tried to
ORDER BY count DESC, p.id DESC
p.id, COUNT(l.id) AS count, p.title_de, p.de
JOIN pages_likes l on l.page_id = p.id
l.date >= DATE_SUB(NOW(),INTERVAL 1 DAY)
l.`status` = 1 GROUP BY p.id
ORDER BY count DESC LIMIT 6
I guess you want count only the records from current day?
>= DATE_SUB(NOW(),INTERVAL 1 DAY)
Remove the time part with
Now the order part: dont use reserved word for alias
SELECT p.id, COUNT(l.id) AS cnt, p.title_de, p.de .... ORDER BY cnt DESC, p.title_de