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

Basic Send Message to MQ with Java and IBM MQ JMS

Basic Receive Message to MQ with Java and IBM MQ JMS

Creating a simple Alert / Success Message with ASP.NET/VB using Bootstrap