Group by but selecting the most recent row

Asked Apr 09 '14 at 17:05

Active Jun 11 '15 at 09:08

Viewed 44 times

So I have a table that I'll simplify like this

AppId, Popularity, Date
1      50          2014-04-09
1      60          2014-04-08
2      45          2014-04-09

I want to get for a bunch of apps at once, their most recent popularity.

I have this query who works locally but takes way to long on production(more than 3 minutes)

SELECT popularity,app_id FROM `popularity`
WHERE `app_id` IN (1..1000) 
AND (`date` = (select max(date) from popularity s where s.app_id = app_id)) 
GROUP BY distribution_application_id

Is there a way to optimize it ?

edited Jun 11 '15 at 09:08

Manuel Allenspach

12,467
14
54
76

asked Apr 09 '14 at 17:05

Paté

1,914
2
22
33

1

have you created indexex in table? – Ehsan Sajjad Apr 09 '14 at 17:07
use having max(date) after group by – danisius Apr 09 '14 at 17:09
use ascending index on date column – Amrit Apr 09 '14 at 17:16

Group by but selecting the most recent row

0 Answers0