Reputation: 49
I'm quite new to t-SQL and are having trouble with what I think is a quite basic query. I'm trying to return all my table columns for a minimum value (price) depending on the destination variable. My current query is:
SELECT min(price) as price, origin, destination FROM prices
GROUP BY origin, destination
ORDER BY destination
Which yields:
price origin destination
392 Baltimore Aarhus
264 Kansas City Aarhus
...
However, I need it to be:
price origin destination
264 Kansas City Aarhus
...
Any ideas?
Upvotes: 0
Views: 74
Reputation: 50173
Use row_number()
:
select p.*
from (select p.*, row_number () over (partition by product, destination order by price) as seq
from prices p
) p
where p.seq = 1;
EDIT : You can use updatable cte for update operation :
WITH U_CTE AS (
SELECT lp.*,
row_number () over (partition by product, destination order by price) as seq
FROM lowest_price lp
)
UPDATE ucte
SET ucte.col = ucte.price
FROM U_CTE ucte
WHERE seq = 1;
Upvotes: 1