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).

Answer 1

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;