GROUP BY PostgreSQL query where I need a column that is not in the GROUP BY clause

Question

I have a database that parallels the 'widget' database below.

widget_id | vendor_id | price
------------------------------
    1     |    101    |  10.00
    2     |    101    |   9.00
    3     |    102    |   6.00
    4     |    102    |   7.00

I want to find the cheapest widget by vendor, so something like the below output:

widget_id | vendor_id | price
------------------------------
    1     |    101    |  10.00
    3     |    102    |   6.00

In MySQL or SQLite, I could query

SELECT widget_id, vendor_id, min( price ) AS price FROM widgets GROUP BY( vendor_id )

However, it seems that this is contrary to the SQL spec. In PostgreSQL, I'm unable to run the above query. The error message is "widget_id must appear in the GROUP BY clause or be used in an aggregate function". I can kind of see PostgreSQL's point, but it seems like a perfectly reasonable thing to want the widget_id of the widget that has the minimum price.

What am I doing wrong?

Your code in MySQL and SQLite is incorrect. It is not guaranteed to return the correct result. — Gordon Linoff, Sep 26 '16 at 22:03

redneb · Answer 1 · 2016-09-26T21:08:43.390

You can use DISTINCT ON:

SELECT DISTINCT ON (vendor_id) *
FROM widget
ORDER BY vendor_id, price;

You can also use the row_number window function in a subquery:

SELECT widget_id, vendor_id, price
FROM (
    SELECT *, row_number() OVER (PARTITION BY vendor_id ORDER BY price) AS rn
    FROM widget
) t
WHERE rn=1;

Finaly, you can also do it with a LATERAL join:

SELECT t2.*
FROM
    (SELECT DISTINCT vendor_id FROM widget) t1,
    LATERAL (SELECT * FROM widget WHERE vendor_id=t1.vendor_id ORDER BY price LIMIT 1) t2

GROUP BY PostgreSQL query where I need a column that is not in the GROUP BY clause

1 Answers1