SQL Find Most Recent Items
Often I want to select the most recent test result for each item from a single table in my database.
For example, say I have table called tab_tests
And within that table I store test results for each item.
Over time I may have several results for the same item
Before I do the next test I wish to check what the most recent result was for each item.
Here is the query I use
SELECT o.itemid, o.testdate, o.testitem,o.nexttestdate, o.lasttestdate, o.testresult
FROM tab_tests AS o
WHERE (((o.testdate)=(SELECT MAX(testdate) FROM tab_tests as i WHERE i.itemid = o.itemid ) )AND ((o.nexttestdate)<=Date()));
For example, say I have table called tab_tests
And within that table I store test results for each item.
Over time I may have several results for the same item
Before I do the next test I wish to check what the most recent result was for each item.
Here is the query I use
SELECT o.itemid, o.testdate, o.testitem,o.nexttestdate, o.lasttestdate, o.testresult
FROM tab_tests AS o
WHERE (((o.testdate)=(SELECT MAX(testdate) FROM tab_tests as i WHERE i.itemid = o.itemid ) )AND ((o.nexttestdate)<=Date()));
Comments
Post a Comment