1
\$\begingroup\$

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;
\$\endgroup\$
1
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – Lucifer Sam Jul 26 '17 at 16:32
  • \$\begingroup\$ @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 :-) \$\endgroup\$ – dnoeth Jul 26 '17 at 17:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.