Max records for a group of kiosks

Question

I am trying to get a list of each ID's top ID record, but when I enter this query:

select max(submitted), t.* from ods.events t where id in

(262, 331, 144, 664, 353, 641, 190, 527, 581) group by id;

I get the following:

+---------------------+-----+---------------------+
|   max(submitted)    | id  |      submitted      |
+---------------------+-----+---------------------+
| 2015-03-17 14:39:53 | 144 | 2014-04-14 04:57:09 |
| 2015-03-03 08:30:15 | 190 | 2014-04-14 01:51:01 |
| 2015-03-17 13:40:14 | 262 | 2014-04-14 03:56:03 |
| 2015-03-17 06:45:10 | 331 | 2014-04-14 05:51:02 |
| 2015-03-17 14:39:41 | 353 | 2014-04-14 05:50:30 |
| 2015-03-17 06:45:39 | 527 | 2014-04-13 21:38:14 |
| 2015-03-17 15:41:32 | 581 | 2014-04-14 05:54:43 |
| 2015-03-17 15:40:26 | 641 | 2014-04-14 05:52:10 |
| 2015-03-17 15:42:10 | 664 | 2014-04-14 05:53:44 |
+---------------------+-----+---------------------+

Apparently it is not bringing the entire record associated with the max(submitted), but some other record. How do I bring in the entire record associated with the max(submitted)?

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html — Abhik Chakraborty, Mar 17 '15 at 20:07
possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Air, Mar 17 '15 at 21:08
Does your `ods.events` contain just `id` and `submitted` or are their other columns you aren't telling us about? — Peter Bowers, Mar 18 '15 at 07:16

score -1 · Answer 1 · answered Mar 17 '15 at 20:06

-1

select submitted, * from events where id in ... group by id having submitted = max(submitted)

answered Mar 17 '15 at 20:06

Stefaan Neyts

2,054
1
16
25

this returns no results – ppadru1 Mar 17 '15 at 20:09

Alex · Answer 2 · 2015-03-17T20:57:04.807

-1

SELECT t.* 
FROM ods.events t 
LEFT JOIN 
    ods.events
   as t2
ON t.id=t2.id
  AND t.submitted < t2.submitted
WHERE t.id IN (262, 331, 144, 664, 353, 641, 190, 527, 581) 
  AND t2.id IS NULL
GROUP BY t.id;

edited Mar 17 '15 at 20:57

answered Mar 17 '15 at 20:07

Alex

16,739
1
28
51

Why the sub-query when you could just do `LEFT JOIN ods.events t2 ON ...`? – Peter Bowers Mar 17 '15 at 20:48
yes, you are right I can do direct join without subquery. Was planing to include WHERE clause in subquery. :-) – Alex Mar 17 '15 at 20:56

score -1 · Answer 3 · answered Mar 17 '15 at 20:30

-1

select t1.submitted, t1.* from events t1 where t1.id in ( ... ) and t1.submitted = select max(t2.submitted) from events t2 where t1.id = t2.id group by t2.id

answered Mar 17 '15 at 20:30

Stefaan Neyts

2,054
1
16
25

Max records for a group of kiosks

3 Answers3