Postgres query for report

Question

I'm trying to solve this problem:

I have a query/view that will join ~10 tables to extract some fields for a report (if any). The query doesn't use any grouping function, only joins and cut off some unuseful data.

I have to take this one big view, get the group for the first index, take the max of a date in the second column and take all the information from other fields referring the record of the max value.

I cannot be able to to this in postgres. As a pseudo code I can give this:

select 1
     , max(2)
     , 3 referred to the record from max(2)
     , 4 referred to the record from max(2)
     , ...
     , 20 referred to the record from max(2)
  from (ViewWithAllJoins) a
group by 1

For privacy and business problem I had to obfuscate some informations, 1/2/3/4... are the name of the column from the view "ViewWithAllJoins", I hope that the problem is still understandable and resolvable!

I've tryied with WINDOW command as reported in Convert keep dense_rank from Oracle query into postgres but I cannot be able to use the group by that I need. Other tryes that I've done was about the dense_rank like shown in Dense_rank first Oracle to Postgresql convert but I can't do any assumption on the order of the data in any of the other fields in exception of 1 and 2, so I can't use any of the aggregate function on them.

Any ideas? Possibly without adding too much subqueryes.

Thank you!

EDIT:

As suggested I'll add some synthetic data to better understand the problem and what I want.

Start:

    ID             DATE            COLUMN1      COLUMN2     COLUMN3
=====================================================================
 88888888;"2016-04-02 09:00:00";"aaaaaaaaaaa";"TEXT89"    ; 999999999
 88888888;"2018-08-21 09:00:00";"a"          ;"TEXT1"     ; 988888888
 88888888;"2017-11-09 09:00:00";"zzzz"       ;"TEXT80000" ; 850580582
 75858585;"2017-01-31 09:00:00";"~~~~~~~~~~~";"TEXT10"    ; 101010101
 75858585;"2018-04-02 09:00:00";"eeeeeeeeeee";"TEXT1000"  ; 111111111
 99999999;"2016-04-02 09:00:00";"8d2ecafd866";"TEXT808911"; 777777777

What I want:

    ID             DATE            COLUMN1      COLUMN2     COLUMN3
===================================================================
 88888888;"2018-08-21 09:00:00";"a"          ;"TEXT1"     ; 988888888
 75858585;"2018-04-02 09:00:00";"eeeeeeeeeee";"TEXT1000"  ; 111111111
 99999999;"2016-04-02 09:00:00";"8d2ecafd866";"TEXT808911"; 777777777

So the group by id, the max of the date and the other fields related to the row of the max date.

@a_horse_with_no_name Sorry, didn't thought about it. Added, I hope it will helps! — Neoeden, Sep 24 '18 at 08:09
So you heve duplicate records per ID, and for every ID you want to select the record with the most recent date ? — joop, Sep 24 '18 at 08:55
@joop basically yes, fields are from different entities / tables — Neoeden, Sep 24 '18 at 11:08

score 0 · Accepted Answer · answered Sep 24 '18 at 11:15

-- So you have duplicate records per ID, and for every ID you want to select the record with the most recent date ?

Use NOT EXISTS:

SELECT id,zdate,column1,column2,column3 -- , ...
FROM queryview t
WHERE NOT EXISTS (
        SELECT *
        FROM queryview x
        WHERE x.id=t.id
        AND x.zdate > t.zdate
        );

Or, use row_number() over a window, and pick only the row with the final date:

SELECT id,zdate,column1,column2,column3 -- , ...
FROM ( SELECT *
        , row_number() OVER(PARTITION BY id, ORDER BY zdate DESC) AS rn
        FROM queryview
        ) q
WHERE q.rn = 1
        ;

Postgres query for report

1 Answers1