Tie-breaking mutliple matches on MAX() in SQL

Question

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.

Does this answer your question? [SQL MAX of multiple columns?](https://stackoverflow.com/questions/71022/sql-max-of-multiple-columns) — philipxy, Sep 21 '22 at 23:29
The linked solution above does not solve this issue because it only returns the max of the three columns. Thus in the sample table data, it would return a `date_of_service` date of 02/02/2022 (because it has the max `data_entry_date` of 02/07/2022) instead of the desired date of 02/04/2022. The max `data_entry_time` is only relevant as a tie breaker when there are multiple rows that match the max `data_entry_date`. Likewise, the max `data_entry_date` is only relevant as a tie breaker when there are multiple rows that match the max `date_of_service`. — jbh006, Sep 23 '22 at 00:18

score 0 · Answer 1 · answered Sep 21 '22 at 23:56

0

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

answered Sep 21 '22 at 23:56

Matt

39
3

This type of selection is going to be replicated for multiple additional tables that will also be linked to the master table. So, I'm not sure if that will work. – jbh006 Sep 22 '22 at 15:27
I had to change `MAX(date_of _service)` to `MAX(date_of_service) AS date_of_service` to get it to work in real life. This seemed to work (I still have to verify the results are correct), but it took several minutes (almost 6 minutes) to run/complete in real world testing on the actual table – jbh006 Sep 22 '22 at 18:02

score 0 · Answer 2 · answered Sep 22 '22 at 02:34

0

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

answered Sep 22 '22 at 02:34

Suresh Gajera

337
1
6

This runs fast (under 6 seconds), but I can't get it to return anything except null values for the date_of_service. I'll keep playing with it just in case I inadvertently did something wonky when I transcribed it for the real world query. – jbh006 Sep 22 '22 at 18:06
I am not sure If I get your question correct. but you can use above code and modify the * with interested columns and use the entire chunk as left join. – Suresh Gajera Sep 22 '22 at 18:51

Tie-breaking mutliple matches on MAX() in SQL

2 Answers2