Re: Newbie MySQL question: how to retrieve latest entry from a history of entries?

From: Flip ([remove)
Date: 08/25/04 

Date: Wed, 25 Aug 2004 14:08:26 -0400

> Can't see how that helps me. The problem is more complicated than you
think.
Sorry. :< I often do that. :< I let my excitement get the better of me. I
don't get to help out often. :>

> I'm not requesting entries for a given date. Instead its the most recent
> date for each station. My original example has been expanded to show how
After looking at your example, I believe I see what you're doing now. I
believe you'll need to do an inner select to narrow down/get the one
specific row you seek, then do the join of that result to the station table.

Depending on the RDBMS you're using, you might be able to limit the rowsize
returned in that inner resultset to just one, otherwise you might be screwed
and need to do it with java in the client. What I mean is, if you're using
something like Oracle, I believe you limit the resultset to a number (1 in
your case I think), but in MS Access (ok, laughing aside, it illustrates my
point :>), you can't do that, and would get everything coming back. To help
yourself there, you'd have to order properly in the resultset to make sure
the highest/most recent metric is at the top for that station and in your
client code, just pull off that top metric for each station.

Another way might be to actually put in a row num into the inner select qry
and in the outter query, only take where rownum = 1.

And yet another way to limit the inner query instead of using a rownum to
limit the resultset, I think is to do a max on that date column. That way,
you're guaranteed to only have one row, it's the biggest (most recent) date,
and then join to station in the outter query.

Hope that helps a bit?