Postgresql - Getting row with max value in column

Question

I'd like to form an sql query, which returns some data of rows, which have max value in some group. Consider the following example for a demonstration:

There are three tables: country, publisher and book. Each publisher belongs to one country and each book has one publisher. Definitions could look like

Country(country_id, country_name)
Publisher(pub_id, pub_name, fk_pub_country)
Book(book_id, book_name, release_date, fk_book_publisher)

I'd like to select (country_id, book_name) grouped by country, so that each row contains the name of the most recently released book in that country. If there are multiple books released on the same day, I should get the one with the highest id.

If I just use group by -clause and max, I cannot include the book name. If I select view (country_id, max_date) and join it with publisher and book, I may receive multiple rows per country. How could I achieve the desired result?

Homework? Add sample table data and the expected result, both as well formatted text. Also show us your current query attempt - and describe what's going wrong. — jarlh
– jarlh, Commented Feb 16, 2017 at 10:51

Nick Barnes · Accepted Answer · 2017-02-16 10:58:24Z

8

SELECT DISTINCT ON (country_id)
  country_id,
  book_name
FROM country
JOIN publisher ON fk_pub_country = country_id
JOIN book ON fk_book_publisher = pub_id
ORDER BY
  country_id,
  release_date DESC,
  book_id DESC

answered Feb 16, 2017 at 10:58

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

eko Over a year ago

Thanks! This also seems to be a good solution considering performance.

Tobb · Accepted Answer · 2017-02-16 10:52:22Z

1

You could use a subquery:

select c.country_id,
       (select b.book_name 
        from Book b 
             join Publisher p on p.pub_id = b.fk_book_publisher 
        where p.fk_pub_country = c.country_id 
        order by b.release_date desc, b.book_id desc limit 1) as book_name
from Country c

answered Feb 16, 2017 at 10:52

Tobb

12.3k6 gold badges60 silver badges83 bronze badges

Comments

Brandon · Accepted Answer · 2018-01-07 02:52:19Z

1

You say:

If I just use group by -clause and max, I cannot include the book name.

But you can't let Postgres boss you around like that. Just grab the first of n sorted records from an array of results like so (untested):

SELECT
  country_id,
  (array_agg(book_name))[1] as book_name
FROM country
JOIN publisher ON fk_pub_country = country_id
JOIN book ON fk_book_publisher = pub_id
GROUP BY country_id ORDER BY release_date DESC;

Since they're ordered how you want, (array_agg(...))[1] grabs the first item from the list and, hey, you got a stew goin'.

answered Jan 7, 2018 at 2:52

Brandon

8,00410 gold badges54 silver badges76 bronze badges

Collectives™ on Stack Overflow

Postgresql - Getting row with max value in column

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related