Simple SQL query with select and group by

Question

I have some kind of problem to understand something.

I have the next table:

ID  PROD    PRICE
1   A        10
2   B        20
3   C        30
4   A        1
5   B        12
6   C        2
7   A        7
8   B        8
9   C        9
10  A        5
11  B        2

I want to get all the minimum prices of all the prod, meaning I want to get 3 records, the minimum price for every prod. From the example above, this is what I want to get:

ID  PROD    MIN(PRICE)
4   A       1
11  B       2
6   C       2

This is the query I wrote:

select id, prod, min(price)
from A1
group by(prod);

But this is the records I got:

ID  PROD    MIN(PRICE)
1   A        1
2   B        2
3   C        2

As you can see the ID value is wrong, it is only give me some kind of line counter and not the actual ID value.

You can check it at the next link

What I'm doing wrong?

Solution 1

SELECT  a.*
FROM    A1 a
        INNER JOIN
        (
            SELECT Prod, MIN(Price) minPrice
            FROM A1
            GROUP BY Prod
        ) b ON a.Prod = b.Prod AND
                a.Price = b.minPrice

• SQLFiddle Demo

For MSSQL

SELECT ID, Prod, Price
FROM
(
  SELECT ID, Prod, Price,
        ROW_NUMBER() OVER(Partition BY Prod ORDER BY Price ASC) s
  FROM A1
) a
WHERE s = 1

• SQLFiddle Demo

Solution 2

You must be using MySQL or perhaps PostgreSQL.

In standard SQL, all non-aggregate columns in the select-list must be cited in the GROUP BY clause.

I'm not clear whether you need the ID column. If not, then use:

SELECT prod, MIN(price) AS min_price
  FROM A1
 GROUP BY prod;

If you need the matching ID number, then that becomes a sub-query:

SELECT id, prod, price
  FROM A1
  JOIN (SELECT prod, MIN(price) AS min_price
          FROM A1
         GROUP BY prod
       ) AS A2 ON A1.prod = A2.prod AND A1.price = A2.min_price;

---

Can you please explain what is the problem with what I wrote, and yes I need the ID column.

select id, prod, min(price)
from A1
group by(prod);

In standard SQL, you would get an error message (or, if not standard, in most SQL DBMS).

Where you are allowed to omit the ID column from the GROUP BY clause, then you get a quasi-random value for ID for the correct prod and MIN(price) values. Basically, the optimizer will choose any convenient ID that it knows about, based on its whims. Specifically, it does not do the sub-query and join that the full answer does. For example, it might do a sequential scan, and the ID it returns might be the first, or last, that it encounters for the given prod value, or it might be some other value — I'm not even sure whether the ID returned for prod = 'A' has to be an ID that was associated with prod = 'A'; you'd have to read the manual carefully. Basically, your query is indeterminate, so many return values are permissible and 'correct' (but not what you wanted).

Note that if you grouped by ID and not prod, then the result in prod would be determinate. That's because the ID column is a candidate key (unique identifier) for the table. (I believe PostgreSQL distinguishes between the two cases — but I'm not certain of that; MySQL does not.)