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

Comments

Popular posts from this blog

Basic Send Message to MQ with Java and IBM MQ JMS

Basic Receive Message to MQ with Java and IBM MQ JMS

Configure Database Connection using MyBatis