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

  1. MeetGeorgeJetson says:

    –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)

             )

  2. WalterM says:

    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

  3. nosnetrom says:

    Great example of a subquery, and just what I needed. Thanks mucho!

  4. MatthewP says:

    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…