My head hurts.
HOW does that query work, Guelph???
AHHHH...Never mind. Figured it out.
WOW! Very clever.
If others want to figure it out, try this variation on it:
Note that I purposely left out the HAVING to make it clearer. And added in a display of COUNT(*) so you can see why it all works.
count(*) as what
yt.subid = yt2.subid
yt.visdate <= yt2.visdate
Now I'm curious: Is this more efficient than the method I proposed??? Does it depend on which fields are indexed?? The HAVING clause there can't be cheap, because it means that the SELECT has to create results for *ALL* possible subid/visdate combinations and then eliminate all those where the COUNT(*) is too big. I'd guess this one depends a *LOT* on how smart the query engine is.
I found another error in my query. A missing GROUP BY:
But yes, my query gives the same results as Guelph's.
FROM yourtable AS MAIN,
( SELECT subid, MAX(visdate) AS oneDate
GROUP BY subid
SELECT T.subid, MAX(T.visdate) AS oneDate
FROM yourtable AS T,
( SELECT subid, MAX(visdate) AS topDate
GROUP BY subid ) AS X
WHERE T.subid = X.subid AND T.visdate < X.topDate
GROUP BY T.subid
) AS U
WHERE MAIN.subid = U.subid AND MAIN.visdate = U.oneDate
ORDER BY MAIN.subid, MAIN.visdate DESC