Alternative to using max() in where-section?

Question

I am pretty new to SQL and am trying to only display the one category which has the most expensive book. My first idea was to remove max(books.purchase_price) from select and add max(books.purchase_price) = books.purchase_price to the where section. But that gives me an error message.

select categories.category, max(books.purchase_price) as price
from books, categories
where categories.category_id = books.category_id
group by categories.category
order by pris desc
limit 1

There are only two tables: books which have entries with titles, prices and category_id; and categories which have category_id and the different categories (names).

the current code "works" but gives me both category and max price. I would want to only show the category-name which have the most expensive book. — Burton, Sep 14 '18 at 10:49
You should check [this](https://www.postgresql.org/docs/9.6/static/queries-with.html) out. — yogkm, Sep 14 '18 at 10:54
What's the expected result if there are two (or more) books having the same, max price? — jarlh, Sep 14 '18 at 11:05

Tim Biegeleisen · Accepted Answer · 2018-09-14T11:16:31.263

If you just want a single max record, then use this query:

SELECT c.category
FROM books b
INNER JOIN categories c
    ON c.category_id = b.category_id
ORDER BY b.purchase_price DESC
LIMIT 1;

This orders the result set by purchase price, in descending order (i.e. most expensive first), then limits to a single record. Note that I used an explicit inner join instead of the implicit old school join you were using above.

If there could be several books tied for the max price, then we would have to resort to using a RANK function:

SELECT DISTINCT category
FROM
(
    SELECT c.category,
        RANK() OVER (ORDER BY b.purchase_price DESC) rank
    FROM books b
    INNER JOIN categories c
        ON c.category_id = b.category_id
) t
WHERE t.rank = 1;

Thank you that was almost exactly what I wanted. I just needed to change the select-section to "select c.category" — Burton, Sep 14 '18 at 10:54

Alternative to using max() in where-section?

1 Answers1