need a query to get the values corresponding to latest dates

[gobis]

Hello everyone,

Briefly stated, I have a table structured like:

VariableName (string) Value (double) Date (date)

Variable names can be repeated (don't worry, in the actual table I
have index numbers that call up the variable names from another
table), so this table contains a limited number of variables' values
that can change independently.

I am using two queries that work together to give the values
corresponding to the latest dates. One query groups by VariableName's
and uses Max(Date) aggregate function to get the latest date, then the
second query joins the first query with the table once again to find
the values corresponding to the latest dates found in the first query.

If all these have made any sense, can anyone tell me a way to
accomplish the same thing in a single query? Joining this table with a
second copy of itself did not help. Not a big deal, but I thought
there might be a simple solution.

Thanks for anyone's time for even reading this.

Take care,
Hurol

 

[John Spencer]

One method use a coordinated sub-query

SELECT VariableName, Value, Date
FROM Table
WHERE Date =
(SELECT Max(Date)
FROM Table as Temp
WHERE Temp.VariableName = Table.VariableName)

Another method uses a sub-query in the from clause
SELECT VariableName, Value, Date
FROM Table INNER JOIN
(SELECT VariableName, Max(Date) as TheLast
FROM Table
GROUP BY VariableName) as TEMP
ON Table.VariableName = Temp.VariableName
And Table.Date = Temp.TheLast



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..