Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

with name( ID, Forename, Surname, Created ) as
(
 select 1,'Tom','Smith','2008-01-01' union all
 select 1,'Tom','Windsor','2008-02-01' union all
 select 2,'Anne','Thorn','2008-01-05' union all
 select 2,'Anne','Baker','2008-03-01' union all
 select 3,'Bill','Sykes','2008-01-20'
)
SELECT id, forename, surname, created
  FROM name n
 WHERE ( id, created ) IN (SELECT id, MAX(created)
                             FROM name
                            GROUP BY id )

 


id
forename
surname
created


1
Tom
Windsor
2008-02-01

2
Anne
Baker
2008-03-01

3
Bill
Sykes
2008-01-20


with name( ID, Forename, Surname, Created ) as
(
 select 1,'Tom','Smith','2008-01-01' union all
 select 1,'Tom','Windsor','2008-02-01' union all
 select 2,'Anne','Thorn','2008-01-05' union all
 select 2,'Anne','Baker','2008-03-01' union all
 select 3,'Bill','Sykes','2008-01-20'
)
SELECT n.id, n.forename, n.surname, n.created
  FROM name n
  RIGHT JOIN ( SELECT id, MAX(created) as created FROM name GROUP BY id ) t
    ON n.created = t.created


id
forename
surname
created


1
Tom
Windsor
2008-02-01

2
Anne
Baker
2008-03-01

3
Bill
Sykes
2008-01-20


with name( ID, Forename, Surname, Created ) as
(
 select 1,'Tom','Smith','2008-01-01' union all
 select 1,'Tom','Windsor','2008-02-01' union all
 select 2,'Anne','Thorn','2008-01-05' union all
 select 2,'Anne','Baker','2008-03-01' union all
 select 3,'Bill','Sykes','2008-01-20'
)
SELECT id, forename, surname, created
  FROM name n
 WHERE EXISTS (SELECT id
                 FROM name
                GROUP BY id
                HAVING MAX(created) = n.created 
                )


id
forename
surname
created


1
Tom
Windsor
2008-02-01

2
Anne
Baker
2008-03-01

3
Bill
Sykes
2008-01-20

id	forename	surname	created
1	Tom	Windsor	2008-02-01
2	Anne	Baker	2008-03-01
3	Bill	Sykes	2008-01-20