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()));


Popular posts from this blog

ActiveMQ, easy to use open source message oriented middleware (MOM)

Basic Send Message to MQ with Java and IBM MQ JMS

Automated Service Monitoring with F5, Consul and Python F5 SDK