SQL Select for most recent record

Suppose I have a table of test results with at least 4 columns:

            ID (unique integer)

            SuiteName (name of test)

            RunTime (DateTime)

            Failed

There are several hundred tests, and I will run a batch file that runs all the tests several times a week.

There may be many failures. I will then rerun only those that failed.

I want a query that will show only the tests that still fail: i.e.: the most recent run was a failure.

Here’s an analogous problem using the Orders table from NorthWind:

Given a table of Orders, I would like a list of customers whose most recent order was not with EmployeeId=3. The # of results should be less than (or equal to) the number of unique customers who placed orders.

Can you come up with a solution? Try before looking at my solution below.

Note the use of an alias for the subquery. (You can use the Temp cursor to compare with the Result cursor to verify the results). The “Try” query shows the most recent orders and has one record per unique customer.

OPEN DATABASE HOME()+"samples\northwind\northwind"

SELECT customerid,orderdate,employeeid FROM orders ORDER BY 1,2 INTO CURSOR temp

BROWSE LAST NOWAIT

SELECT CustomerId,MAX(OrderDate) FROM Orders GROUP BY 1 INTO CURSOR Try

BROWSE LAST NOWAIT

SELECT Orders.CustomerId,Orders.OrderDate,Orders.EmployeeId FROM Orders INNER JOIN ;

      (SELECT CustomerId, MAX(OrderDate) as maxd FROM Orders GROUP BY CustomerId) as subq ;

      ON subq.CustomerId = Orders.Customerid AND subq.maxd = Orders.OrderDate ;

      WHERE Orders.EmployeeId<>3 ;

      ORDER BY Orders.CustomerId INTO CURSOR result

BROWSE LAST NOWAIT

Actually, my data is in SQL server, and the query runs in a web app (see What is a Microsoft MVP? Calvin's List updated for the new century). As you can see, the Fox SQL and SQL Server syntax are the same.

Here’s the actual running code:

      cWhereExprSrv=" trun.failed='Y' and runtime >= CAST(?cDateRecent as datetime)"

      IF SQLEXEC(hConn,"Select trun.SuiteName from trun inner join (select bb.suitename,MAX(bb.runtime) as maxr from trun as bb "+ ;

                  "group by bb.suitename) subq ON subq.suitename=trun.suitename and subq.maxr=trun.runtime where " + ;

                  cWhereExprSrv,"StillFail") < 0

            AERROR(aa)

            THROW "Err StillFail query '"+cWhereExprSrv+"' "+aa[1,2]

      ENDIF

      CursorToHTML("Suites that still fail","")

See also SQL Select puzzle to count failures and totals for each item

Comments

• Anonymous
  July 16, 2007
  --Alias tInner is not really necessary below --but is added for clarity --Alias tOuter is required --For most recent run OF EACH SuiteName, --select the failures SELECT * FROM tblTests tOuter WHERE   (tOuter.Failed = 1) AND (tOuter.RunTime =          (          SELECT Max(tInner.RunTime)          FROM tblTests tInner          WHERE            (tInner.SuiteName=tOuter.SuiteName)          ) --For most recent run OF ANY SuiteName --i.e. --the last RunTime identified (shaky) batch run, --select the failures SELECT * FROM tblTests tOuter WHERE   (tOuter.Failed = 1) AND (tOuter.RunTime =          (          SELECT Max(tInner.RunTime)          FROM tblTests tInner          --comment out where clause          --WHERE          -- (tInner.SuiteName=tOuter.SuiteName)          )

• Anonymous
  March 10, 2008
  Why not something as simple as SELECT * FROM Order O WHERE NOT EXISTS(SELECT 1 FROM Orders WHERE CustomerID = O.Customerid AND OrderDate > O.Orderdate) AND employeeid <> 3

• Anonymous
  May 27, 2009
  Great example of a subquery, and just what I needed. Thanks mucho!

• Anonymous
  July 02, 2009
  I think there is one problem with the solution presented.  What if the customer has more than one order that was ordered on the same day?  Obtaining the MAX date I think should return one record, but when you join that with the orders that happened on the same date, all of the orders on that date would be returned.  In that instance, the most recent order would be indeterminate.  It might be helpful if the order had its own ID and it was known that the ID would increase with every order placed...  Aye...so complicated...