Select max date in two columns with group by in postgresql-CodePudding

I am trying to select all columns form extract table where I need to select the values which are with latest Upload_Date and latest date_modification among ID column. Basically I want to select max dated values in each ID.

Tried with below qeuiry but getting error as "more than one row returned by a subquery used as an expression"

SELECT *
   FROM public.extract
  WHERE extract."Upload_Date" = (( SELECT max(extract."Upload_Date")
           FROM public.extract))  
           AND extract."date_modification" = (( 
               SELECT max(extract."date_modification")
                FROM public.extract
            group by extract."ID" ));

I am new to postgresql. It will be helpful can someone suggest where I am making mistake.

Sample Data looks like

Expected output is like this

CodePudding user response：

Use DISTINCT ON:

SELECT DISTINCT ON (ID) *
FROM public.extract
ORDER BY ID, Upload_Date DESC, date_modification DESC;