1

I'm having the following query, which supposedly will get the latest 3 comments for every update_id. It seemingly works until I discovered a logical issue where it gets the latest 3 comments regardless the update; it is supposed to get the latest 3 comments for every update_id. How can I achieve this? Obviously the limit(3) is wrong. 

SELECT  `comms` . * ,  `usr`.`name` 
FROM (
`comms`
)
JOIN  `users` AS usr ON  `usr`.`id` =  `comms`.`user_id` 
WHERE  `update_id` 
IN (
'1451',  '1416',  '1186',  '1157',  '1150',  '1122',  '1057',  '914',  '850',  '816',  '794',  '790',  '749',  '746',  '745',  '744',  '740'
)
ORDER BY  `id` DESC 
LIMIT 3
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
Ando
  • 1,802
  • 4
  • 25
  • 47
  • If you want three results how is LIMIT 3 wrong? – Nev Stokes Jan 13 '14 at 08:19
  • Because i have 10 updates so the latest 3 would multiply to 30 rather than 3. I want to show the latest 3 per update_id. – Ando Jan 13 '14 at 08:21
  • This may help: [Using LIMIT within GROUP BY to get N results per group?](http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group) – Aziz Shaikh Jan 13 '14 at 08:22
  • Try ORDER BY `update_id` DESC instead of `id` – ASR Jan 13 '14 at 08:24

1 Answers1

1

Try this: 

SELECT A.*, u.name 
FROM (SELECT c.*, IF(@updateId = @updateId:=update_id, @idx:=@idx+1, @idx:=0) idx 
      FROM comms c, (SELECT @updateId:=0, @idx:=0) A
      WHERE update_id IN ('1451', '1416', '1186', '1157', '1150', '1122', '1057', '914', '850', '816', '794', '790', '749', '746', '745', '744', '740')
      ORDER BY update_id, id DESC   
    ) AS A 
INNER JOIN users AS u ON u.id = A.user_id
WHERE A.idx < 3
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83