# Combination aggregate/greatest-n-per-group SQL query on simplified data set

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.

SQL Fiddle

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;


A common solution is based on Window Functions, as all three are based on the same window they're calculated in a single step:

SELECT url, page_count, word_count, article_id
FROM
(
SELECT
url,
article_id,
-- both aggregations rewritten as Group Aggregates
count(*)         over (partition by article_id) as page_count,
sum(word_count)  over (partition by article_id) AS word_count,
-- find the minimum page number
page_number =
min(page_number) over (partition by article_id) as is_min_page
FROM page
) as dt
WHERE is_min_page; -- return minimum page only


See fiddle

• Thank you, this is awesome! Prior to asking the question I actually read the PostgreSQL docs on Window Functions but I've never used them before and couldn't conceptualize how to apply it here since I was stuck on using the GROUP BY. Also seems even easier to read if a WINDOW clause is used since each function is using the same window. I'll leave this open for now in case anyone else wants to chime in but will mark as accepted at the end of the day. – Lucifer Sam Jul 26 '17 at 16:32
• @LuciferSam: Yep, PostgreSQL allows defining a WINDOW for multiple use (I usually work with Teradata which doesn't support it), but in your case (only partition by) it simplifies it only a bit. Those Window Functions were the best addition to SQL ever, they might greatly reduce complexity in lots of cases, you just have to get used to them :-) – dnoeth Jul 26 '17 at 17:07