select biggest date difference in mysql-CodePudding

I have this query

select ps.trial_id, ps.submit_date, pa.approved_date, TIMESTAMPDIFF(DAY,ps.submit_date, 
pa.approved_date) as dias_p_aprovar from picolo_submit ps join picolo_approved pa on   
ps.trial_id = pa.trial_id where ps.submit_date < pa.approved_date order by ps.trial_id;

and this is a sample of result:

|    12418 | 2022-01-27 20:09:58 | 2022-01-28 15:54:37 |              0 |
|    12418 | 2022-01-28 15:52:25 | 2022-01-28 15:54:37 |              0 |
|    12419 | 2022-01-20 22:29:41 | 2022-01-27 16:43:23 |              6 |
|    12419 | 2022-01-25 15:07:27 | 2022-01-27 16:43:23 |              2 |
|    12419 | 2022-01-27 14:45:46 | 2022-01-27 16:43:23 |              0 |
|    12419 | 2022-01-27 15:42:09 | 2022-01-27 16:43:23 |              0 |
|    12439 | 2022-01-26 16:41:24 | 2022-01-28 17:31:19 |              2 |
|    12439 | 2022-01-27 15:42:20 | 2022-01-28 17:31:19 |              1 |
|    12439 | 2022-01-27 18:16:11 | 2022-01-28 17:31:19 |              0 |
|    12439 | 2022-01-28 14:12:17 | 2022-01-28 17:31:19 |              0 |
|    12439 | 2022-01-28 17:15:16 | 2022-01-28 17:31:19 |              0 |
 ---------- --------------------- --------------------- ---------------- 
6911 rows in set (0,06 sec)

I need to select the result of the biggest TIMESTAMPDIFF of each ps.trial_id. In the case of ps.trial_id = 12439 i need only the first line.

|    12439 | 2022-01-26 16:41:24 | 2022-01-28 17:31:19 |              2 |

Is it possible?

CodePudding user response：

On MySQL 8 , we can use ROW_NUMBER for this:

WITH cte AS (
    SELECT ps.trial_id, ps.submit_date, pa.approved_date,
           TIMESTAMPDIFF(DAY, ps.submit_date, pa.approved_date) AS dias_p_aprovar,
           ROW_NUMBER() OVER (PARTITION BY ps.trial_id
                              ORDER BY TIMESTAMPDIFF(DAY, ps.submit_date, pa.approved_date) DESC) rn
    FROM picolo_submit ps
    INNER JOIN picolo_approved pa ON ps.trial_id = pa.trial_id
    WHERE ps.submit_date < pa.approved_date
)

SELECT trial_id, submit_date, approved_date, dias_p_aprovar
FROM cte
WHERE rn = 1
ORDER BY trial_id;