Tie-breaking mutliple matches on MAX() in SQL

jbh006 0
Viewed 60

I have a table that looks like this:

| client_id | program_id | provider_id | date_of_service | data_entry_date | data_entry_time |
| --------- | ---------- | ----------- | --------------- | --------------- | --------------- |
|         2 |          5 |           6 | 02/02/2022      | 02/02/2022      | 0945            |
|         2 |          5 |           6 | 02/02/2022      | 02/07/2022      | 0900            | 
|         2 |          5 |           6 | 02/04/2022      | 02/04/2022      | 1000            |
|         2 |          5 |           6 | 02/04/2022      | 02/04/2022      | 1700            |
|         2 |          5 |           6 | 02/04/2022      | 02/05/2022      | 0800            |
|         2 |          5 |           6 | 02/04/2022      | 02/05/2022      | 0900            |

I need to get the most recent date_of_service entered. From the table above, the desired result/row is:

date_of_service = 02/04/2022, data_entry_date = 02/05/2022, data_entry_time = 0900

This resulting date_of_service will be left joined to the master table.

This query mostly works:

SELECT t1.client_id, t1.program_id, t1.provider_id, t2.date_of_service
FROM table1 as t1
WHERE provider_id = '6'

LEFT JOIN
  (SELECT client_id, program_id, provider_id, date_of_service
  FROM table2) as t2
  ON t2.client_id = t1.client_id
  AND t2.program_id = t1.program_id
  AND t2.provider_id = t1.provider_id
  AND t2.date_of_service = 
    (SELECT MAX(date_of_service)
    FROM t2 as t3
    WHERE t3.client_id = t1.client_id
    AND t3.program_id = t1.program_id
    AND t3.provider_id = t1.provider_id
    )
  )

But it also returns multiple rows whenever there is more than one match on the max(date_of_service).

To solve this, I need to use the max data_entry_date to break any ties whenever there is more than one row that matches the max(date_of_service). Likewise, I also need to use the max data_entry_time to break any ties whenever there is more than one row that also matches the max data_entry_date.

I tried the following:

SELECT t1.client_id, t1.program_id, t1.provider_id, t2.date_of_service
FROM table1 as t1
WHERE provider_id = '6'

LEFT JOIN
  (SELECT TOP(1) client_id, program_id, provider_id, date_of_service, data_entry_date, data_entry_time
  FROM table2
  ORDER BY date_of_service DESC, data_entry_date DESC, data_entry_time DESC
  ) as t2
  ON t2.client_id = t1.client_id
  AND t2.program_id = t1.program_id
  AND t2.provider_id = t1.provider_id

But I can only get it to return null values for the date_of_service.

Likewise, this:

SELECT t1.client_id, t1.program_id, t1.provider_id, t2.date_of_service
FROM table1 as t1
WHERE provider_id = '6'

LEFT JOIN
  (
    SELECT TOP(1) client_id AS client_id2, program_id AS program_id2, provider_id AS provider_id2, date_of_service, data_entry_date, data_entry_time
    FROM table2 AS t3
    JOIN 
     (SELECT
      MAX(date_of_service) AS max_date_of_service
     ,MAX(data_entry_date) AS max_data_entry_date
      FROM table1
      WHERE date_of_service = (SELECT MAX(date_of_service) FROM table2)
      ) AS t4
    ON t3.date_of_service = t4.max_date_of_service
    AND t3.data_entry_date = t4.max_data_entry_date
    ORDER BY data_entry_time
  ) AS t2
  ON t2.client_id2 = t1.client_id
  AND t2.program_id2 = t1.program_id
  AND t2.provider_id2 = t1.provider_id

... works (meaning it doesn't throw any errors), but it only seems to return null values for me.

I've tried various combinations of MAX, ORDER BY, and multiple variations of JOIN's, but haven't found one that works yet.

I don't know what version my SQL database is, but it doesn't appear to handle window functions like OVER and PARTITION or other things like COALESCE. I've been using DBeaver 22.2.0 to test the SQL scripts.

left-join max sql-order-by sql
2 Answers

Based on your what you've provided, looks like you can simply query table2:

SELECT client_id, program_id, provider_id, MAX(date_of_service), MAX(data_entry_date), MAX(data_entry_time)
FROM table2
GROUP BY client_id, program_id, provider_id

If you need to join this result set to table1, just JOIN to the statement above on client_id, program_id, provider_id

user6cVl0

Try using below query. This is using just joins and sub query.

SELECT TOP 1 * FROM table1 t1
JOIN (
       SELECT 
                 MAX(date_of_Service) AS Max_date_of_Service
                ,MAX(data_entry_date) AS Max_data_entry_date 
       FROM  table1
       WHERE date_of_Service = (SELECT MAX(date_of_Service) FROM table1)
     )t2
ON   t1.date_of_Service = t2.Max_date_of_Service
AND  t1.data_entry_date = t2.Max_data_entry_date
ORDER BY data_entry_time
Suresh Gajera0
Related