I've been reading the top 'greatest-n-per-group' answers on StackOverflow but haven't yet found any answers that fit my specific case.
The following code works, but it seems overly-complicated for what I want to achieve. The nested/self-joined derived tables seem wrong but I don't know how to simplify things without resorting to a correlated sub-query.
Goal: For each article, I want to select the url (as determined by the page with the lowest page_number
), page count and word count.
Schema:
-- tables
CREATE TABLE article (
id int PRIMARY KEY
);
CREATE TABLE page (
id int PRIMARY KEY,
url text,
page_number int,
word_count int,
article_id int REFERENCES article
);
-- data
INSERT INTO article (id) VALUES (1), (2), (3);
INSERT INTO page (id, url, page_number, word_count, article_id) VALUES
(1, '/1/1', 1, 250, 1), (2, '/1/2', 2, 230, 1), (3, '/1/3', 3, 55, 1),
(4, '/2/1', 1, 300, 2),
(5, '/3/1', 1, 1000, 3), (6, '/3/2', 2, 1000, 3);
Query:
SELECT
first_page.url,
count(*) AS page_count,
sum(page.word_count) AS word_count,
page.article_id
FROM
page
JOIN (
SELECT
page.url,
page.article_id
FROM
page
JOIN (
SELECT
min(page_number) AS page_number,
article_id
FROM page
GROUP BY article_id
) AS first_page ON
first_page.article_id = page.article_id AND
first_page.page_number = page.page_number
) AS first_page ON first_page.article_id = page.article_id
GROUP BY
page.article_id,
first_page.url;