CODEWITHSUNDEEP
sqldatabase
johnklee
johnklee

Reputation: 2270

Group query and top n records SQL

If I have a table with columns pid, sid as primary key, and price and color.

How do I create a SQL query that can fetch the cheapest price of each pid (product ID) which has color equal to green?

For example I have rows :

(1, 1, 10, "green")
(1, 2, 8, "green")
(1, 3, 11, "green")
(2, 1, 7, "green")
(2, 2, 15, "green")
(2, 3, 22, "green")
(3, 1, 10, "red")
(1, 2, 8, "red")
(1, 3, 11, "red")

Then I will receive : (1, 2, 8)->pid=1 and (2, 1, 7)->pid=2. No pid=3 because its color is red.

Thanks in advance.

Upvotes: 0

Views: 104

Answers (2)

John Woo
John Woo

Reputation: 263713

Try this,

SELECT  a.*
FROM    tableName a
            INNER JOIN
            (
                SELECT pid, min(price) minPrice
                FROM    tableName b
            ) c ON a.pid = c.pid AND
                    a.price = c.minprice
WHERE   a.color = 'green'

Upvotes: 0

podiluska
podiluska

Reputation: 51494

SELECT pid, min(Price)
from table
Where Color='green'
group by pid

Or if you want the whole row (and your sql platform supports ranking)...

select * 
from
(
 select *, ROW_NUMBER() over (partition by pid order by price) rn
 from #t
 where color='green'
) v
where rn=1

Upvotes: 1

Related Questions