Selecting multiple max values in SQL-CodePudding

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      | 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. In other words: the maximum date_of_service with the maximum data_entry_date with the maximum data_entry_time.

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 will be left joined to the master table.

My SQL query looks something like the following, but I can't get it to return the desired date of service result.

SELECT table1.client_id, table1.program_id, table1.provider_id, note.date_of_service
FROM table1
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 note
  ON note.client_id = table1.client_id
  AND note.program_id = table1.program_id
  AND note.provider_id = table1.provider_id

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.

CodePudding user response：

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

CodePudding user response：

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