SQL Select for most recent record

  • Article

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