Vaia Vaia - 24 days ago 6
MySQL Question

Show most popular items of today?

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
but I think this didn't seem to work using
GROUP BY
I assume.

__

This is my code:

SELECT
p.id, COUNT(l.id) AS count, p.title_de, p.de
FROM
pages p
JOIN pages_likes l on l.page_id = p.id
WHERE
l.date >= DATE_SUB(NOW(),INTERVAL 1 DAY)
AND
l.`status` = 1 GROUP BY p.id
ORDER BY count DESC LIMIT 6


This outputs for example:

enter image description here

Answer

I guess you want count only the records from current day?

instead of

 >= DATE_SUB(NOW(),INTERVAL 1 DAY) 

Remove the time part with DATE()

SELECT DATE(NOW());

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
Comments