Select only the latest date from joined table

[SamA74]

I can’t seem to figure this one out. I think it should be possible in a single mysql query, but I don’t have it yet.
The main table contains rows of things I am fetching as objects via PDO.
The second table contains a list of dates when those things have been updated.
I want to select columns from the main table, and the latest date for each row in the main table, from the second table.

The initial query looked something like this:-

SELECT area, seamarks.id, name, type, description, seamarks.lat, seamarks.lon, date
			FROM seamarks
			LEFT JOIN changes on seamarks.id = mid
			WHERE active = '1'

This of course gave me duplicates where any row from the first table has more than one change in the changes table. I only want one with the newest date.

If I try:-

SELECT area, seamarks.id, name, type, description, seamarks.lat, seamarks.lon, date
			FROM seamarks
			LEFT JOIN changes on seamarks.id = mid
			WHERE active = '1'
                        GROUP BY mid ORDER BY date DESC

…I lose the duplicates, but the dates are not the latest.
The other issue I have with using GROUP BY is that I am also using the PDO::FETCH_GROUP mode (targeting ‘area’ to group by), which causes a conflict, though they are not the same thing.

$this->dbdata = $this->dbread->query($query)->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_CLASS, 'seamark');

Any idea how to make this work, without a second query, or weeding through results to remove duplicates?

[Martyr2]

Take a look at the MAX function which you can use to get the most recent date. You should be able to do something similar to this…

SELECT area, seamarks.id, name, type, description, seamarks.lat, seamarks.lon, MAX(date)
			FROM seamarks LEFT JOIN changes on seamarks.id = mid WHERE active = '1' GROUP BY seamarks.id

Now obviously this is will need some testing on your side, but the solution should work fine. Here we are saying get the MAX DATE. To make this also give the max date for each item in seamarks, we are also saying group by the seamarks ID field. Without this, you would most likely get one max date for the entire table, not the max date for each item. With the group by we group all duplicates together based on the same seamarks.id and from that grouping, return the max date for each group.

I hope you get what I am saying. If you need some more info, research into MAX summary function and how it works with the group by clause. Examples of MAX usage should shed light on how this would work.

[SamA74]

Thanks, I’ve finished for this evening, so will take a closer look tomorrow.

[SamA74]

It appears to be working, thank you.
I was just a bit concerned that the grouping may interfere with the pdo group fetch mode, as it seemed to when I first tried it to remove the duplicates, but the data seems to be coming our OK now,as far as I can see.

@Martyr2

look at the MAX function

I really need to learn more sql functions, there are so many I must be overlooking.