I'm sure its a simple question and I suppose it was asked many times, but I just can't figure it out from other answers, sorry.
I use recent versions of PostgreSQL and MySQL.
I have 2 tables:
CREATE TABLE authors (
id INT,
name VARCHAR
)
CREATE TABLE posts (
id INT,
author_id INT,
text VARCHAR,
date DATE
)
I need to select one most recent post for each author. Thanks!
UPDATE
Thanks, both links provide answers to my question with some exception.
All the following queries give the same result (which one is the most efficient btw?) The issue is when there is more than one post from the same author with the same date. Then the returned result set contains all such posts. How should I modify these queries to return exactly one post per author?
SELECT p1.*
FROM posts p1
LEFT JOIN posts p2 ON p1.author_id = p2.author_id AND p1.date < p2.date
WHERE p2.author_id IS NULL
ORDER BY p1.author_id;
SELECT p1.*
FROM posts p1
INNER JOIN (
SELECT author_id, MAX(date) AS max_date
FROM posts
GROUP BY author_id) p2
ON p1.author_id = p2.author_id AND p1.date = p2.max_date
ORDER BY p1.author_id;
SELECT *
FROM posts p1
WHERE date = (SELECT MAX(p2.date)
FROM posts p2
WHERE p1.author_id = p2.author_id)
ORDER BY author_id;
SELECT * FROM (
SELECT author_id, MAX(date) date
FROM posts GROUP BY author_id
) p1 INNER JOIN posts p2 USING (author_id, date)
ORDER BY author_id;
Best Answer
If you aim is to have queries with maximum efficiency, none of the above queries is really the best. Not always at least.
Efficiency depends on many different things, like the specific DBMS, the specific version (different versions have different improvements on the optimizer and the available syntax), the type of columns, the indexes available, the size of the tables and distribution of values, the hardware the server is running, the configuration settings etc.
You should always test various different ways of writing the queries, on your tables, with the sizes and distribution you expect to have on production, with your hardware and configuration settings, to decide which rewritings of the queries should be kept.
This specific kind of query is often called
greatest-n-per-group
(there is even a tag for it!) and under certain assumptions, one of the many ways to write them, is often quite efficient in both MySQL and PostgreSQL. It uses aLATERAL
join in Postgres, which is available in 9.3+ versions (in SQL Server lingoCROSS/OUTER APPLY
) and a simulation of this join in MySQL.The assumptions are that the number of authors (the attribute we group by on) is small, compared to the number of posts (the table where we apply the group by). It's also best if there is an index or a table to find all the distinct
author_id
values and an additional index on theposts
table for the group by.This solution to the greatest-n-per-group problem matches also your request about ties, as it returns always one result per group. If you want to be precise about which one (of the tied) will be returned, the
ORDER BY
in the subquery can be modified (toORDER BY pi.date DESC, pi.id DESC
orORDER BY pi.date DESC, a.name
for example).Query in PostgreSQL:
Query in MySQL:
The useful index is on
posts (author_id, date, id)
for MySQL and or onposts (author_id, date DESC)
for Postgres.Needless to say again but before using any of the above, they should be tested in your environment and cross tested against all the many other versions/rewritings of the query. In Postgres for example, the
DISTINCT ON
syntax can be used in version older than 9.3. The resulting query is more compact than theLATERAL
and might be more efficient, under different data distributions. Query: