mysql - group by the last record

Question

My table has multiple records of n_no column, and when I GROUP BY, it returns me the first record of the group, how do I get the last record?

SELECT * FROM mytable WHERE category = 16 AND status = 1 AND datetime >= "2020-01-06 00:00:00" AND datetime <= "2020-01-06 23:59:59" GROUP BY n_no ORDER BY datetime DESC LIMIT 0,30

Thanks.

[Solved by doing]

SELECT * FROM mytable WHERE (SELECT MAX(datetime) from mytable GROUP BY n_no) AND category = 16 AND status = 1 AND datetime >= "2020-01-06 00:00:00" AND datetime <= "2020-01-06 23:59:59" GROUP BY n_no ORDER BY datetime DESC LIMIT 0,30

Why do you have a `GROUP_BY` when you have no aggregation functions? — Nick, Jan 07 '20 at 03:01

score 1 · Answer 1 · answered Jan 07 '20 at 03:08

1

Assuming you are using MySQL 8+, then ROW_NUMBER() can work here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY n_no ORDER BY datetime DESC) rn
    FROM mytable
    WHERE category = 16 AND status = 1 AND
          datetime >= '2020-01-06' AND datetime < '2020-01-07'
)

SELECT *
FROM cte
WHERE rn = 1;

answered Jan 07 '20 at 03:08

Tim Biegeleisen

502,043
27
286
360

unfortunately, i'm in version 5 – hatched Jan 07 '20 at 03:17

Lemmy Kilmister · Answer 2 · 2020-01-07T04:39:57.930

0

The idea of "GROUP BY" is to get categories. not to select a record.

SELECT DISTINCT n_no, datetime, category, status, etc 
FROM mytable 
WHERE category = 16 
AND status = 1 
AND datetime BETWEEN "2020-01-06 00:00:00" AND "2020-01-06 23:59:59" 
ORDER BY datetime DESC
LIMIT 1

in this way you get all records from each n_no with the last date

edited Jan 07 '20 at 04:39

answered Jan 07 '20 at 03:10

Lemmy Kilmister

27
4

i have to group by a column `n_no` , and return each latest record of different `n_no` – hatched Jan 07 '20 at 03:20
@hatched I updated my code a bit, check it out. – Lemmy Kilmister Jan 07 '20 at 04:52

score 0 · Answer 3 · answered Jan 07 '20 at 04:27

You can try something like below,

SELECT * FROM mytable WHERE pk_col in (SELECT max(pk_col) FROM mytable WHERE category = 16 AND status = 1 AND datetime >= "2020-01-06 00:00:00" AND datetime <= "2020-01-06 23:59:59" GROUP BY n_no ORDER BY datetime DESC LIMIT 0,30)

Here, replace pk_col with the primary column name of your table.

Please find a sample here.

mysql - group by the last record

3 Answers3