I would normally query the date with sub query like this.
(Presuming client is the foreign key in contactlog table)
SELECT
c.first,
c.last,
(SELECT cl.date FROM contactlog AS cl WHERE cl.client=c.id ORDER BY cl.date DESC LIMIT 1) AS logdate
FROM
clients AS c
ORDER BY
logdate ASC;
SELECT cl.latest
, c.first
, c.last
FROM clients AS c
LEFT OUTER
JOIN ( SELECT client
, MAX(`date`) AS latest
FROM contactlog
GROUP
BY client ) AS cl
ON cl.client = c.id
ORDER
BY latest ASC
In rajugs’ query he’s ordering by date desc, date might not be indexed.
In r937 - result is obtained by using aggregate function, again on a non-indexed column.
I assume date isn’t indexed.
If the “cid” column is auto_increment here, why not just order by id desc limit 1? No aggregate function involved, no additional index needed.
Thing is, since it isn’t mentioned explicitly, date of last contact will have the highest cid column unless there’s a certain application-level feature preventing it from being so.
It’s not that I’m disagreeing on the use of auto_increments, but there is a possibility that every new entry for the client contact contains date that’s later than the previous entry - hence, there’s nothing preventing us from using cid as means of obtaining the latest date
Also, if it is such feature, we don’t need the composite index of cid and date.
If the feature isn’t such, then I’d definitely go with r937’s query after making the
suggested index change.
say you have a large table, and you insert into it daily
then it is discovered that you missed one day’s inserts, and it’s now 3 days later
all you have to do is insert the missing data
… unless, of course, you’ve chained yourself to the ridiculous notion that higher auto_increment numbers mean later dates – in that case, you will first have to delete all the stuff inserted after the missing day’s data, then add them all back in the right sequence
then it is discovered that you missed one day’s inserts, and it’s now 3 days later
And how does exactly this miss occur? Magically out of the blue and then the system continues working?
And who discovers the error 3 days later?
If you enter an entry each time you contacted the client, the date of contact will be the latest one. Consequently, for that client, the auto_increment will be the largest one too, in the table that stores the time of contact. We can go over catastrophic disaster plan here, what if this and that but I’m still not wrong and if something crashes in such way that even application logic fails - there’s a much bigger problem at hand then.
If the application logic is as the one I described, why not use the shortcut and you avoid indexing another field? Yes, there are potential errors but I’d really like to see them happening before saying I’m wrong :>
You guys are talking past each other. Blue is correct if the chronological age of the records independent of their insertion sequence isn’t important, but if it is important an insertion timestamp of some sort should be kept, which case r937 is right.
In short, you each mean something slightly different by the phrase “latest entry.”
I’d go with the timestamp approach like r937 though - it’s rarely useful to know the actual insertion sequence of the records, importing historical data being a key example of how trying to use auto index as a chronological record can go horribly wrong.
