clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335936 fiddles created (27472 in the last week).

CREATE TABLE users ( id SERIAL PRIMARY KEY, name TEXT ); CREATE TABLE notes ( id SERIAL PRIMARY KEY, user_id INT, content TEXT, created_at TIMESTAMP, CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(id) ); CREATE index ids_notes_user_id_created_at ON notes(user_id, created_at);
 hidden batch(es)


INSERT INTO users(name) VALUES ('Alice'), ('Bob'), ('Charlie'); INSERT INTO notes(user_id, content, created_at) VALUES (1, 'Alice Note 1', '2021-01-01T15:30:00Z'), (2, 'Bob Note 1', '2021-01-02T13:00:00Z'), (3, 'Charlie Note 1', '2021-01-01T10:00:00Z'), (1, 'Alice Note 2', '2021-02-01T11:00:00Z'), (2, 'Bob Note 2', '2021-01-20T12:00:00Z'), (1, 'Alice Note 3', '2021-03-01T13:00:00Z');
3 rows affected
6 rows affected
 hidden batch(es)


SELECT * FROM notes;
id user_id content created_at
1 1 Alice Note 1 2021-01-01 15:30:00
2 2 Bob Note 1 2021-01-02 13:00:00
3 3 Charlie Note 1 2021-01-01 10:00:00
4 1 Alice Note 2 2021-02-01 11:00:00
5 2 Bob Note 2 2021-01-20 12:00:00
6 1 Alice Note 3 2021-03-01 13:00:00
 hidden batch(es)


SELECT * FROM users;
id name
1 Alice
2 Bob
3 Charlie
 hidden batch(es)


SELECT latest_notes.* FROM notes AS latest_notes LEFT JOIN notes AS user_notes ON latest_notes.user_id = user_notes.user_id AND user_notes.created_at > latest_notes.created_at WHERE user_notes.id IS NULL;
id user_id content created_at
6 1 Alice Note 3 2021-03-01 13:00:00
5 2 Bob Note 2 2021-01-20 12:00:00
3 3 Charlie Note 1 2021-01-01 10:00:00
 hidden batch(es)


SELECT notes.* FROM ( SELECT user_id, MAX(created_at) AS created_at FROM notes GROUP BY notes.user_id ) AS latest_notes INNER JOIN notes ON notes.user_id = latest_notes.user_id AND notes.created_at = latest_notes.created_at
id user_id content created_at
3 3 Charlie Note 1 2021-01-01 10:00:00
5 2 Bob Note 2 2021-01-20 12:00:00
6 1 Alice Note 3 2021-03-01 13:00:00
 hidden batch(es)


EXPLAIN ANALYZE SELECT latest_notes.* FROM notes AS latest_notes LEFT JOIN notes AS user_notes ON latest_notes.user_id = user_notes.user_id AND user_notes.created_at > latest_notes.created_at WHERE user_notes.id IS NULL;
QUERY PLAN
Merge Left Join (cost=0.30..231.27 rows=10 width=48) (actual time=0.039..0.045 rows=3 loops=1)
Merge Cond: (latest_notes.user_id = user_notes.user_id)
Join Filter: (user_notes.created_at > latest_notes.created_at)
Rows Removed by Join Filter: 10
Filter: (user_notes.id IS NULL)
Rows Removed by Filter: 4
-> Index Scan using ids_notes_user_id_created_at on notes latest_notes (cost=0.15..64.20 rows=1070 width=48) (actual time=0.016..0.018 rows=6 loops=1)
-> Materialize (cost=0.15..66.88 rows=1070 width=16) (actual time=0.013..0.017 rows=14 loops=1)
-> Index Scan using ids_notes_user_id_created_at on notes user_notes (cost=0.15..64.20 rows=1070 width=16) (actual time=0.007..0.009 rows=6 loops=1)
Planning Time: 0.111 ms
Execution Time: 0.068 ms
 hidden batch(es)


EXPLAIN ANALYZE SELECT notes.* FROM ( SELECT user_id, MAX(created_at) AS created_at FROM notes GROUP BY notes.user_id ) AS latest_notes INNER JOIN notes ON notes.user_id = latest_notes.user_id AND notes.created_at = latest_notes.created_at
QUERY PLAN
Hash Join (cost=33.05..59.37 rows=5 width=48) (actual time=0.041..0.043 rows=3 loops=1)
Hash Cond: ((notes.user_id = notes_1.user_id) AND (notes.created_at = (max(notes_1.created_at))))
-> Seq Scan on notes (cost=0.00..20.70 rows=1070 width=48) (actual time=0.009..0.010 rows=6 loops=1)
-> Hash (cost=30.05..30.05 rows=200 width=12) (actual time=0.018..0.018 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> HashAggregate (cost=26.05..28.05 rows=200 width=12) (actual time=0.014..0.016 rows=3 loops=1)
Group Key: notes_1.user_id
Batches: 1 Memory Usage: 40kB
-> Seq Scan on notes notes_1 (cost=0.00..20.70 rows=1070 width=12) (actual time=0.005..0.006 rows=6 loops=1)
Planning Time: 0.157 ms
Execution Time: 0.091 ms
 hidden batch(es)