S.M_Emamian S.M_Emamian - 1 month ago 6
MySQL Question

How to get most downloads records

I have two tables :

wallpapers
:

id img cat_id


downloads
:

id wall_id
1 10
2 10
3 2
. .


I would like to get all wallpapers that have most downloads from a specific
cat_id
.

my query :

SELECT *
FROM wallpapers
WHERE cat_id = 10
AND id IN
(SELECT wall_id
FROM (SELECT wall_id, count(*)
FROM downloads
GROUP BY wall_id
ORDER BY count(*) DESC))


but it returns nothing !




my framework is
Codeingiter
:

$rows = $this->db->query($sql);

var_dump($rows);
return;


output :

bool(false)

Answer

just group them by wall_id in that way you can get each wall_id total downloads and then order them by them count

ORDER BY count(downloads.wall_id) DESC

and add limit

LIMIT 3

assuming you want to get the top 3 most downloads
so your final query would be like this

SELECT   * 
FROM     wallpapers 
JOIN     downloads 
ON       wallpapers.id = downloads.wall_id 
GROUP BY downloads.wall_id ORDER BY count(downloads.wall_id) DESC limit 3
Comments