Rows with max value of each group

Question

I'm trying to get rows that have max value in each group.

I have this tables

+-------+-----------+--------------+-----------+
|  nid  |     name  |   crated     | grp_id    |
+-------+-----------+--------------+-----------+
|  1    | RAND_NAME |     123      |        11 |
|  2    | EHllo     |     111      |        11 |
|  3    | Stop by   |     444      |        11 |
|  4    | Radr c    |     555      |        11 |
|  5    | NAE       |     666      |        22 |
|  6    | ABC       |     1234     |        22 |
|  7    | RAND      |     123      |        22 |
|  8    | YELLO     |     444      |        22 |
|  9    | AAA       |     555      |        33 |
|  10   | WWW       |     1235     |        33 |
|  11   | ADF       |     553      |        33 |
+-------+-----------+--------------+-----------+

So, I want this table

+-------+-------------+------------+-----------+
| nid   |     name    |  created   | grp_id    |
+-------+-------------+------------+-----------+
|   3   | Radr c      |   555      |        11 |
|   6   | ABC         |   1234     |        22 |
|  10   | WWW         |   1235     |        33 |
+-------+-------------+------------+-----------+

which means I want to grab rows that have the highest created value in each group. The table will be grouped by grp_id.

I was thinking this way:

SELECT nid, name, created, grp_id
   FROM table t
   WHERE t.created = (SELECT MAX(t1.created) FROM table t1)
   GROUP BY grp_id
   ORDER BY grp_id

But, it didn't work out. What should I do to get three different rows that have the highest created value in each group?

Thank you for understanding about my poor explanation.

Why is 444 > 555 in grp 11? – Sentinel Nov 13 '15 at 20:43 — Sentinel, Nov 13 '15 at 20:43
Sorry, mistake. changed the value. – PHG Nov 16 '15 at 14:01 — PHG, Nov 16 '15 at 14:01

score 1 · Accepted Answer · answered Nov 13 '15 at 20:43

1

You need a subselect:

SELECT yourtable.*
FROM yourtable
LEFT JOIN (
    SELECT grp_id, MAX(created) AS max
    FROM yourtable
    GROUP BY grp_id
) AS maxgroup ON (
    (yourtable.grp_id = maxgroup.grp_id) AND (yourtable.created = maxgroup.max)
)

subselect the gets the ID/max value for each group, and the parent/outer query joins agains the subselect results to get the rest of the fields for the row(s) that the max value appears on.

answered Nov 13 '15 at 20:43

Marc B

356,200
43
426
500

why `left join` over `inner join` ? – Juan Carlos Oropeza Nov 13 '15 at 20:45
force of habit on my part. most of the queries I write tend to be left joins. doesn't matter in this particular case, since you KNOW the data's going to be available on both sides, since "both" sides have the same source data. – Marc B Nov 13 '15 at 20:47

Deric Miller · Answer 2 · 2015-11-13T20:53:36.910

-1

Try:

SELECT nid, name, MAX(created), grp_id FROM t GROUP BY grp_id;

edited Nov 13 '15 at 20:53

answered Nov 13 '15 at 20:48

Deric Miller

92
4

This will probably work in `mySql` but is very far from ANSI SQL – Juan Carlos Oropeza Nov 13 '15 at 20:55

score -1 · Answer 3 · edited Nov 14 '15 at 02:03

-1

SELECT nid, name, MAX(created), grp_id
FROM table
GROUP BY grp_id
order by MAX(nid);

edited Nov 14 '15 at 02:03

Pang

9,564
146
81
122

answered Nov 13 '15 at 20:55

HashSu

1,507
1
13
13

Rows with max value of each group

3 Answers3