MIN() function

sorpippo

Hello folks!
I have a table like this

pid| sid | desc | price |
1 2 ss 10
1 3 ff 15
2 2 dd 19
2 3 xx 20

How can I select, within the same pid, the lowest price? My goal is to obtain a result like this
pid | sid | desc | price |
1 2 ss 10
2 2 dd 19

Ciao.
Giuseppe.

Anon

try
select pid,sid,desc,min(price)
from table
group by pid,sid,desc

sorpippo

With
select pid,sid,desc,min(price) from borsa where insid=12 group by pid,sid,desc;
+-----+-----+-------------+-------------+
| pid | sid | descr | min(price) |
+-----+-----+-------------+-------------+
| 4 | 11 | aa | 10 |
| 4 | 12 | aa | 15 |
| 5 | 11 | aa | 15 |
| 5 | 12 | aa | 14 |
+-----+-----+-------------+-------------+
The result is merely sorted.
I would like to obtain the lowest price for each pid. A thing like this
+-----+-----+-------------+-------------+
| pid | sid | descr | min(price) |
+-----+-----+-------------+-------------+
| 4 | 11 | aa | 10 |
| 5 | 12 | aa | 14 |
+-----+-----+-------------+-------------+

I tried with
SELECT pid,sid,min(prezzo), descrizione FROM borsa where insid=12 GROUP BY pid ORDER BY prezzo DESC;

But I obtain
+-----+-----+-------------+-------------+
| pid | sid | min(prezzo) | descrizione |
+-----+-----+-------------+-------------+
| 4 | 12 | 10 | aa |
| 5 | 12 | 14 | aa |
+-----+-----+-------------+-------------+

The row with the pid 4 has the wrong sid (12 instead of 11; see the previous table). viceversa, if I GROUP BY sid, the sid is correct but pid is incorrect.
+-----+-----+-------------+-------------+
| pid | sid | min(prezzo) | descrizione |
+-----+-----+-------------+-------------+
| 4 | 12 | 14 | aa |
| 4 | 11 | 10 | aa |
+-----+-----+-------------+-------------+

How can I solve the problem?

Ciao.

Giuseppe.

Anon

select * from borsa b
where prezzo = (select min(prezzo) from borsa where pid = b.pid)

[deleted]

You cannot select columns that are not in the MIN() or GROUP BY clause.

THis is possible:

SELECT id, min(col3)
FROM table
GROUP BY id;

this is not:

SELECT id, col2, min(col3)
FROM table
GROUP BY id;

So you will have to do more than one query.
Are you using mysql or a real database?

A forum, a FAQ, email notification, what else do you need?