Select row with smallest number on multiple groups of same ids

Question

I have the following table as an output from a sql statement

user | product | price 
          …
123  | 12     | 451.29
373  | 12     | 637.28
623  | 12     | 650.84
672  | 16     | 356.87
123  | 16     | 263.90
          …

Now I want to get only the row with the smallest price for each product_id

THE SQL is fairly easy

SELECT user, product, price
        FROM t
        WHERE product IN (
            SELECT product_id
            FROM p
            WHERE typ LIKE 'producttyp1'
        )
)

but adding MIN(price) does not work how it usually do. I think its because there are several groups of the same product_ids in the same table. Is there an easy to use solution or do I have to rewrite the whole query?

Edit: when I delete user from the query I can get the product and the smallest price:

12 | 451.29
16 | 263.90

But now I would have to join the user, which I am trying to avoid.

. . Your query really has nothing to do with getting the smallest price. I'm wondering what you are thinking (`price` isn't even mentions in the query). — Gordon Linoff, Nov 24 '20 at 18:24
Does this answer your question? [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Clockwork-Muse, Nov 26 '20 at 05:56

score 1 · Accepted Answer · answered Nov 24 '20 at 18:23

1

You can use row_number():

select p.*
from (select p.*,
             row_number() over (partition by product order by price asc) as seqnum
      from p
     ) p
where seqnum = 1;

answered Nov 24 '20 at 18:23

Gordon Linoff

1,242,037
58
646
786

does this solution mean that I just take the first row from each group? which has the smallest price after ordering it – Tim4497 Nov 24 '20 at 18:31
@Tim4497 . . . You explained that quite well. – Gordon Linoff Nov 24 '20 at 23:16

Select row with smallest number on multiple groups of same ids

1 Answers1