single SQL query that returns the most recent values

[bill1313]

I'm currently working on a component tracking file and we are using an hour meter query to always have up to date information. My problem is that I need the hour meter query to display the reading of the most recent date for each equipment number.

My columns are:
EQUIP_NO
DATE_READING
CONDITION
LIFE_TO_DATE

I want a single SQL query that, given an EQUIP_NO, returns only the most recent DATE_READING, CONDITION and LIFE_TO_DATE. I have looked at a few posts about this but am still having trouble getting it to work.

Any help on this matter would be great. Thanks

[ronverdonk]

The following query returns only 1 of the latest (newest date_reading) row.

SELECT * FROM table WHERE equip_no='xxxx' ORDER BY date_reading DESC LIMIT 1;

Ronald :cool:

[bill1313]

The following query returns only 1 of the latest (newest date_reading) row.

SELECT * FROM table WHERE equip_no='xxxx' ORDER BY date_reading DESC LIMIT 1;

Ronald :cool:

This only returns the data for one machine because you have to specify the machine number. My query needs to return all 100 machines that we have and give the most recent reading of each machine. Say for example that the last time a reading was taken on DH031 was 2/25/2007 but most other machines have a reading on 3/7/2007. I need my query to be able to pick the most recent date reading for each machine and return the corresponding data.

In the full query (without an addition of code to SQL) there are readings for each date. But if a machine was parked or being worked on, then there will not be a reading for that day and the life to date hours would be the same. The problem here is that if there hasn't been a reading for 2 weeks for example, then that machine's last dated entry would be 2 weeks ago. It would not show up on the query if it's being filtered for 7/3/2007. Most code that i have tried to insert only shows values for the most recent reading of the entire data.

Here's a little example of the full hour meter reading query:

EQUIP_NO DATE_READING CONDITION LIFE_TO_DATE

DH028 3/7/2007 FTD 521400
DH028 3/6/2007 FTD 521200
DH028 3/5/2007 FTD 521000
DH028 3/4/2007 FTD 520750
and so on.... depending on how far you wanted to go back
DH031 2/27/2007 FTD 421030
DH031 2/26/2007 FTD 420900
DH031 2/25/2007 FTD 420500
and so on

So I need my query to return the most recent reading for each machine. As in this example, I would want

EQUIP_NO DATE_READING CONDITION LIFE_TO_DATE

DH028 3/7/2007 FTD 521400
DH031 2/27/2007 FTD 421030

But as i mentioned previously we have about 100 machines so would need 1 entry for the last time there was a reading for each machine. I hope this clarifies my problem and thank you to whoever lends a hand.

[nicolasn]

Bill

This should (i.e. is untested) do the trick...

select
T1.EQUIP_NO
T2.DATE_READING
T2.CONDITION
T2.LIFE_TO_DATE
from
(select distinct EQUIP_NO from MY_EQUIPMENT) as T1
inner join MY_EQUIPMENT as T2
on T1.EQUIP_NO=T2.EQUIP_NO
and T2.DATE_READING=(select max(DATE_READING) from MY_EQUIPMENT where EQUIP_NO=T1.EQUIP_NO)

I found your post while trying to solve basically the same problem. I found my solution in the article titled "Only the latest matching row" here: [link removed]

Regards,
Nicolas

[gisps]

This only returns the data for one machine because you have to specify the machine number. My query needs to return all 100 machines that we have and give the most recent reading of each machine. Say for example that the last time a reading was taken on DH031 was 2/25/2007 but most other machines have a reading on 3/7/2007. I need my query to be able to pick the most recent date reading for each machine and return the corresponding data.

In the full query (without an addition of code to SQL) there are readings for each date. But if a machine was parked or being worked on, then there will not be a reading for that day and the life to date hours would be the same. The problem here is that if there hasn't been a reading for 2 weeks for example, then that machine's last dated entry would be 2 weeks ago. It would not show up on the query if it's being filtered for 7/3/2007. Most code that i have tried to insert only shows values for the most recent reading of the entire data.

Here's a little example of the full hour meter reading query:

EQUIP_NO DATE_READING CONDITION LIFE_TO_DATE

DH028 3/7/2007 FTD 521400
DH028 3/6/2007 FTD 521200
DH028 3/5/2007 FTD 521000
DH028 3/4/2007 FTD 520750
and so on.... depending on how far you wanted to go back
DH031 2/27/2007 FTD 421030
DH031 2/26/2007 FTD 420900
DH031 2/25/2007 FTD 420500
and so on

So I need my query to return the most recent reading for each machine. As in this example, I would want

EQUIP_NO DATE_READING CONDITION LIFE_TO_DATE

DH028 3/7/2007 FTD 521400
DH031 2/27/2007 FTD 421030

But as i mentioned previously we have about 100 machines so would need 1 entry for the last time there was a reading for each machine. I hope this clarifies my problem and thank you to whoever lends a hand.

Did you ever get an answer to your question? I'm having the same problem with no luck. Once I group other columns into the query it returns multiple records of the history. Max works fine for only one column.

[Mellisa]

Thank you for posting that, nicolasn. I have been searching for a solution for this issue for some time, and this is the only one that has worked.