how to select max value and return many entries

Question

Suppose I have such a table :

theme    module  changed
0        1       1426070691
1        1       1426070300
0        1       1324014321
1        2       1241245585
1        1       1015421251

I need a SQL query that returns for the same theme and module the max value of changed:

theme    module  changed
0        1       1426070691
1        1       1426070300
1        2       1241245585

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) — Uwe Allner, Mar 12 '15 at 08:53
possible duplicate of [SQL : Using GROUP BY and MAX on multiple columns](http://stackoverflow.com/questions/4045609/sql-using-group-by-and-max-on-multiple-columns) — Michael Jaros, Mar 12 '15 at 08:57
Unfortunately the solutions provided on the duplicates referenced are both poor/incomplete. SO's got to get better at this. — Strawberry, Mar 12 '15 at 08:59

Abhik Chakraborty · Answer 1 · 2015-03-12T09:05:01.243

1

You can use left join

select 
t1.* from table_name t1
left join table_name t2 
on t1.theme = t2.theme 
and t1.module = t2.module
and t1.changed > t2.changed
where t2.theme is null

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

edited Mar 12 '15 at 09:05

answered Mar 12 '15 at 08:55

Abhik Chakraborty

44,654
6
52
63

1

old school - i like it. Bit slow though – Strawberry Mar 12 '15 at 08:57
1

Why can't you just use `GROUP BY`? – Salman A Mar 12 '15 at 09:01

score 1 · Accepted Answer · answered Mar 12 '15 at 09:03

1

You simply need to GROUP your results by theme and module:

SELECT theme, module, MAX(changed)
FROM table_name
GROUP BY theme, module

answered Mar 12 '15 at 09:03

Salman A

262,204
82
430
521

how to select max value and return many entries

2 Answers2