Find the lastest qualifying row for each key

Question

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?

Can you post the query you've currently got, also can you clarify why you want rows 1 and 11, not just 11 (11 has 2015-03-23, which is later than 1's 2015-01-23)? — GHC, Mar 31 '15 at 10:50
Shouldn't that be *only* row 11? Why is row 1 included? It would make sense to include 1 if `date_deb` were 2015-03-23. — Gordon Linoff, Mar 31 '15 at 10:52
i want to get the list of sites (chantier) that are closed (date_fin is not null) and the last worked chief for each site — SlimenTN, Mar 31 '15 at 11:10
Typically, it is better to provide data as text. We cannot copy / paste from an image. And remember to provide your version of Postgres. — Erwin Brandstetter, Mar 31 '15 at 11:17

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

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

Select first row in each GROUP BY group?

Depending on data distribution there may be faster solutions:

Optimize GROUP BY query to retrieve latest record per user

edited May 23 '17 at 11:50

Community

1
1

answered Mar 31 '15 at 11:10

Erwin Brandstetter

605,456
145
1,078
1,228

Thnx it's what I'm looking for – SlimenTN Mar 31 '15 at 11:13

score 0 · Answer 2 · answered Mar 31 '15 at 10:51

0

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;

answered Mar 31 '15 at 10:51

Gordon Linoff

1,242,037
58
646
786

I believe that ASC will give the output he listed as expected – Dmitrij Kultasev Mar 31 '15 at 10:55
@sidux . . . The question asks for the last `date_deb`. – Gordon Linoff Apr 01 '15 at 18:10
question - yes, but the expected output - no. – Dmitrij Kultasev Apr 02 '15 at 06:43

score 0 · Answer 3 · answered Mar 31 '15 at 11:33

-- 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
    );

Find the lastest qualifying row for each key

3 Answers3