MySQL Select Latest Date - Single Table

Question

I have a table that looks like this and I want to get the IDs (insp_id) of the newest insp_date for each loc_id.

create table insp (
    insp_id int (10),
    loc_id int (11),
    insp_type varchar (150),
    insp_date date ,
    insp_active tinyint (2),
    insp_created timestamp ,
    insp_modified timestamp 
);

I tried the "IN" strategy from Select latest record in table (datetime field) and others but that gives me double since one loc_id's latest date may be the non-latest for another:

SELECT insp_id, loc_id, insp_active, insp_date
FROM insp 
WHERE insp_active = 1 
AND insp_date IN(SELECT MAX(insp_date) FROM insp WHERE insp_active = 1 GROUP BY loc_id)
ORDER BY loc_id ASC, insp_date DESC;

I setup a SQL Fiddle but adding various GROUP BY and MAX does not seem to get it. I feel like I need to join on a sub-query or something similar but am not sure at this point.

http://sqlfiddle.com/#!2/f95e0/1

Thanks, Andrew

For the record, similar thing was raised here: http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column — ptrk, Dec 05 '14 at 21:53

score 1 · Answer 1 · answered Dec 05 '14 at 18:04

1

Do a join and aggregation:

select i.*
from insp i join
     (select loc_id, max(isp_date) as maxid
      from insp
      group by loc_id
     ) lm
     on i.loc_id = lm.loc_id and i.isp_date = lm.maxid;

You can still run the risk of duplicates, if the maximum date is repeated for the same location.

answered Dec 05 '14 at 18:04

Gordon Linoff

1,242,037
58
646
786

Thanks, I tacked in the insp_active = 1 clause (I forgot to mention that specifically in the ?) and it works great. Looks like the same as @fabien-thesolution... wish I could give equal credit. – Andrew T Dec 05 '14 at 18:28

Fabien TheSolution · Accepted Answer · 2014-12-05T18:17:14.327

You need to retrieve the MAX date for each location, and then, the MAX insp_id for this date

Query 1:

SELECT insp.loc_id, max(insp.insp_id)
FROM insp
INNER JOIN
    (SELECT loc_id, max(insp_date) as insp_date
     FROM insp 
     WHERE insp_active = 1 
     GROUP BY loc_id) AS mdate ON mdate.loc_id = insp.loc_id AND 
                                  mdate.insp_date = insp.insp_date
WHERE insp_active = 1 
GROUP BY insp.loc_id

Results:

| LOC_ID | MAX(INSP.INSP_ID) |
|--------|-------------------|
|      1 |                 1 |
|      2 |                40 |
|      3 |                48 |
|      4 |                37 |
|      5 |                49 |
|      6 |                39 |
|      7 |                50 |
|      8 |                46 |

UPDATE :

If there is only one active inspection for a specific date, and this is always the newest that is active, you can just do this :

SELECT insp.loc_id, insp.insp_id
FROM insp
INNER JOIN
    (SELECT loc_id, max(insp_date) as insp_date
     FROM insp 
     WHERE insp_active = 1 
     GROUP BY loc_id) AS mdate ON mdate.loc_id = insp.loc_id AND 
                                  mdate.insp_date = insp.insp_date
WHERE insp_active = 1

Saw the update and not always the case for my use but see where it could be for others. Nice idea. — Andrew T, Dec 05 '14 at 18:24

score 0 · Answer 3 · answered Dec 05 '14 at 18:05

0

SELECT insp_id 
FROM insp
WHERE insp_active = 1
GROUP BY loc_id
ORDER BY insp_date DESC;

answered Dec 05 '14 at 18:05

Riftus

59
1
10

Thanks, this did not work right for me even though I feel like it should. Appreciate the thought but think I got it with the other solutions now. – Andrew T Dec 05 '14 at 18:26
http://sqlfiddle.com/#!2/f95e0/18 Verifies that it gives you the same result (albeit in a different order) as the answer you chose – Riftus Dec 05 '14 at 18:35
When I go to that or run the SQL on mine it gives me insp_id 45 for loc_id 2 but the answer I chose correctly gives insp_id 40 for loc_id 2. – Andrew T Dec 05 '14 at 19:09

ptrk · Answer 4 · 2014-12-05T21:47:21.940

0

Sub-query is the most by-the-book solution, but I used to get away with something less standard, as it was before nesting was allowed in MySQL:

select max(concat_ws('|', insp_date, insp_id)) as result, loc_id
from insp
group by loc_id;

You'd get something like:

2014-01-01|12
2014-02-14|19
...

Then you obviously need to split it in your app. Note it's very MySQL-dialect specific and not very performant, but nested queries are not either (or at least used not to be).

http://sqlfiddle.com/#!2/f95e0/23/0

edited Dec 05 '14 at 21:47

answered Dec 05 '14 at 18:20

ptrk

1,800
1
15
24

Thanks, definitely a unique approach to keep in mind for future ideas. – Andrew T Dec 05 '14 at 18:26

MySQL Select Latest Date - Single Table

4 Answers4