CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
SlimenTN
SlimenTN

Reputation: 3564

Find the lastest qualifying row for each key

I have this table in my database:

enter image description here

I want to get 2 columns: id_chantier and id_chef.
Conditions: date_fin not null and has the last date_deb.
So the rows that I want to get are number 1 and 11.
How can I do that?

Upvotes: 1

Views: 63

Answers (3)

joop
joop

Reputation: 4503

-- I want to get the list of sites (chantier) that are closed (date_fin is not null)

SELECT *
FROM ztable t
WHERE date_fin IS NOT NULL
AND NOT EXISTS (
    SELECT * FROM ztable nx
    WHERE nx.id_chantier = t.id_chantier -- Same site
    AND nx.date_fin > t.date_fin         -- more recent
    );

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

SELECT DISTINCT ON (id_chef)
       id_chantier, id_chef
FROM   tbl
WHERE  date_fin IS NOT NULL
ORDER  BY id_chef, date_deb DESC NULLS LAST;

Details for DISTINCT ON

Depending on data distribution there may be faster solutions:

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can do this with rank():

select id_chantier, id_chef
from (select t.*, rank() over (order by date_deb desc) as rnk
      from table t
     ) t
where date_fin is not null and rnk = 1;

Upvotes: 0

Related Questions