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:

Desired table for table4:

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

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