Home > Blockchain >  How to obtain recent date/time using MAX() function if I can not put it in the "where" cla
How to obtain recent date/time using MAX() function if I can not put it in the "where" cla

Time:08-27

I am trying to select the recent date and time. I understand that I cannot use MAX() function in the where clause because it will not work. For my table 4, I do not want to use "GROUP BY" statement as I want to see all the ID's. I tried using this code below but it gave me one row instead of multiple rows from Table 3. If I can not use the MAX() function in the where clause, what other ways can I obtain the most recent date and time?

Table 3:

ID RESULT_DATE1 RESULT_TIME_1 FINAL_DATE1 FINAL_TIME_1
766 11/13/2020 12:12:12 11/29/2020 13:11:12
766 11/13/2020 12:12:12 11/29/2020 14:11:12
765 10/13/2020 12:12:12 10/14/2020 11:11:12
765 10/20/2021 12:12:12 10/21/2020 12:13:14

Desired table for table4:

ID RESULT_DATE1 RESULT_TIME_1 FINAL_DATE1 FINAL_TIME_1
766 11/13/2020 12:12:12 11/29/2020 14:11:12
765 10/20/2021 12:12:12 10/21/2020 12:13:14

Code:

CREATE TABLE table4 AS
SELECT *, 
        MAX(RESULT_DATE1)
FROM table3
/*WHERE RESULT_DATE1 || ' ' || RESULT_TIME_1*/
ORDER BY ID 
;

CodePudding user response:

Here is a simple solution using row_number.

select ID   
      ,RESULT_DATE1
      ,RESULT_TIME_1
      ,FINAL_DATE1  
      ,FINAL_TIME_1
from  (
       select *
              ,row_number() over (partition by ID order by FINAL_DATE1 desc,FINAL_TIME_1 desc) as rn
       from t
       ) t
where rn = 1
ID RESULT_DATE1 RESULT_TIME_1 FINAL_DATE1 FINAL_TIME_1
765 10/20/2021 12:12:12 10/21/2020 12:13:14
766 11/13/2020 12:12:12 11/29/2020 14:11:12

Fiddle

CodePudding user response:

You can use a windowing function like ROW_NUMBER() to partition the results by the ID and descending order them by the dates, choosing the first result for each partition, like so:

WITH Numbered AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY RESULT_DATE_1 DESC, RESULT_TIME_1 DESC) RowNumber
FROM table4
)

SELECT * FROM Numbered WHERE RowNumber = 1

Or, you could GROUP BY and JOIN the results to the original table:

WITH MaxDate AS
(
SELECT ID, MAX(RESULT_DATE_1) LatestDate
FROM table4
GROUP BY ID
)

SELECT *
FROM table4 T
JOIN MaxDate M
ON 
    T.ID = M.ID
    AND T.RESULT_DATE_1 = M.LatestDate
  • Related