Given these entries in my table:

id date value
4 2002-1-1 zero
4 2002-2-26 first
4 2002-2-28 second

I am trying to write a SQL query to get the value at the maximum date. So I first tried this query and got:

select MAX(date),value from MY_TBL group by id

2002-2-28 first

When it does the aggregate function of group by and then chooses a maximum date, it disreguards other fields. It then selects the first entry to show
the value of.

So it is even worse than that, because what I would really like to do is, get the latest value on a given day.

So here is an example:

id date value
6 2002-2-2 zero
6 2002-2-16 first
6 2002-2-18 second
6 2002-3-2 third

select MAX(date),value from MY_TBL where date < '2002-3-2' group by id

This result doesn't return me anything! Which would lead me to the same conclusion above. mysql groups everything first, decides the maximum value
(in this case '2002-3-2' and then applies the where clause and since '2002-3-2' is not less than the date i was looking for it doesn't report anything!

Is their a way to change the order of operations so that is first evaluates the where clause and then does the group/max?

A way using a single query to get the value at a maximum date?

Thanks for the help.

Sincerely,
Andrew Mirsky
ajmirsky@hotmail.com