SQL Querying Column on Max Date

Question

I apologize if my code is not properly typed. I am trying to query a table that will return the latest bgcheckdate and status report. The table contains additional bgcheckdates and statuses for each record but in my report I only need to see the latest bgcheckdate with its status.

SELECT BG.PEOPLE_ID, MAX(BG.DATE_RUN) AS DATERUN, BG.STATUS
FROM PKS_BGCHECK BG
GROUP BY BG.PEOPLE_ID, BG.status;

When I run the above query, I still see queries with multiple background check dates and statuses.

Whereas when I run without the status, it works fine:

SELECT BG.PEOPLE_ID, MAX(BG.DATE_RUN)
FROM PKS_BGCHECK BG
GROUP BY BG.PEOPLE_ID;

So just wondering if anyone can help me figure out help me query the date run and status and both reflecting the latest date.

So do you want to know the status for each of the maximum BG.Date_Runs? — Greg the Incredulous, Dec 23 '14 at 21:21
I am using Oracle 11g, and yes please I need to know the status (can be null) for each max BG.Date_Runs — Sam, Dec 23 '14 at 21:24

score 1 · Accepted Answer · edited May 23 '17 at 12:12

The best solution depends on which RDBMS you are using.

Here is one with basic, standard SQL:

SELECT bg.PEOPLE_ID, bg.DATE_RUN, bg.STATUS
FROM  (
   SELECT PEOPLE_ID, MAX(DATE_RUN) AS MAX_DATERUN
   FROM   PKS_BGCHECK
   GROUP  BY PEOPLE_ID
   ) sub
JOIN PKS_BGCHECK bg ON bg.PEOPLE_ID = sub.PEOPLE_ID
                   AND bg.DATE_RUN = sub.MAX_DATERUN;

But you can get multiple rows per PEOPLE_ID if there are ties.

In Oracle, Postgres or SQL Server and others (but not MySQL) you can also use the window function row_number():

WITH cte AS (
   SELECT PEOPLE_ID, DATE_RUN, STATUS
        , ROW_NUMBER() OVER(PARTITION BY PEOPLE_ID ORDER BY DATE_RUN DESC) AS rn
   FROM   PKS_BGCHECK
   )
SELECT PEOPLE_ID, DATE_RUN, STATUS
FROM   cte
WHERE  rn = 1;

This guarantees 1 row per PEOPLE_ID. Ties are resolved arbitrarily. Add more expressions to ORDER BY to break ties deterministically.

In Postgres, the simplest solution would be with DISTINCT ON.

Details for both in this related answer:

Select first row in each GROUP BY group?

score 0 · Answer 2 · answered Dec 27 '14 at 00:37

Selecting the latest row in a time-sensitive set is fairly easy and largely platform independent:

SELECT BG.PEOPLE_ID, BG.DATE_RUN, BG.STATUS
FROM   PKS_BGCHECK BG
WHERE  BG.DATE_RUN =(
        SELECT MAX( DATE_RUN )
        FROM   PKS_BGCHECK
        WHERE  PEOPLE_ID = BG.PEOPLE_ID
          AND  DATE_RUN < SYSDATE );

If the PK is (PEOPLE_ID, DATE_RUN), the query will execute about as quickly as any other method. If they don't form the PK (why not???) then use them to form a unique index. But I'm sure you're already doing one or the other.

Btw, you don't really need the and part of the sub-query if you don't allow future dates to be entered. Some temporal implementations allow for future dates (planned or scheduled events) so I'm used to adding it.

SQL Querying Column on Max Date

2 Answers2