'Group by two column with min condition

I have next table:

| ID |ID2  | price|
+----+---+----+
| A  | AA   | 7 |
| B  | AA   | 3 |
| C  | AA   | 4 |
| D  | BB   | 7 |
| I  | BB   | 2 |
| F  | BB   | 3 |
| G  | CC   | 9 |
| E  | CC   | 4 |
| K  | CC   | 1 |

+----+---+---+

And I need to get the next table group by ID2 with min field price and corresponding min price field ID1

| ID1 | ID2 | min_price |
+----+---+---+----------
| B  | AA   | 3         |
| I  | BB   | 2         |
| K  | CC   | 1         |
+----+---+---+---------
mysqlsql


Solution 1:[1]

select a.id, a.id2, a.price from #yourtable a
 join 
    (
    select  id2, min(price) AS price 
    from #yourtable
    group by id2 
    )b 
 on a.id2=b.id2 and a.price=b.price

Solution 2:[2]

SELECT *
FROM
    #table t
    INNER JOIN (
       SELECT
          ID2
          ,MIN(price) as min_price
       FROM
          #table
       GROUP BY
          ID2
    ) g
    ON t.ID2 = g.ID2
    AND t.price = g.min_price

Step 1 find the minimum price, step 2 relate back to get the original record that matches to get the ID. There are other ways to do this as well.

Note that if your dataset contains 2 records in the same ID2 with the same price then more than 1 record will always be returned.

Solution 3:[3]

Based on your explanation, we need to group by id2 and get the lowest price and its corresponding id. Here is the query for the same

SELECT id,
  id2,
  price
FROM NEXT
WHERE (price,id2) IN
  (SELECT MIN(price), id2 FROM NEXT GROUP BY id2
  );

Solution 4:[4]

http://sqlfiddle.com/#!9/295702/2

SELECT t1.*
FROM t1
LEFT JOIN t1 t2
ON t1.id2=t2.id2
  AND t1.price>t2.price
WHERE t2.ID IS NULL

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tudor Saru
Solution 2 Matt
Solution 3 Avani
Solution 4 Alex

Related Questions