Max () function question 1

[pachad]

I have the following info in a table:
[tt]
|---------| Year | Rate |
| Plant A | 2005 | 5.00 |
| Plant A | 2006 | 4.00 |
| Plant B | 2005 | 3.50 |
[/tt]

Not every plant has its rate information changed every year.

What query can I use to ensure I always get the most current info for a plant?

I tried using a select query, setting the WHERE to MAX(Year) but that only returns the rate for records showing 2006--the highest year in the table.

I want the output to show

[tt]
Plant A 2006 4.00
Plant B 2005 3.50
[/tt]

Thanks.

PS If there is a better way to store this info, please let me know that as well.

 

[PHV]

Something like this (SQL code) ?
SELECT A.Plant, A.Year, A.Rate
FROM yourTable AS A INNER JOIN (
SELECT Plant, Max([Year]) AS LastYear FROM yourTable GROUP BY Plant
) AS B ON A.Plant = B.Plant AND A.Year = B.LastYear

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pachad]

PH,

Your idea worked great, and I still need to do more research on INNER JOIN. I tried the code below, and it also seemed to work (your use of AS keyed me to it). Will this work the same as your solution, since it uses WHERE and IN which I am more familier with.

Thanks.

SELECT A.Plant, A.Year, A.Rate
FROM yourTable AS A WHERE
A.Year IN (SELECT MAX(Year) from yourTable as B WHERE A.Plant = B.Plant)

 

[PHV]

Yes, your solution should work ([pedantic]it is the correlated subquery way [/pedantic]).
I guess mine should be faster with a huge table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pachad]

Great.

Thanks for the quick response.