CODEWITHSUNDEEP
sqlsql-servert-sql
random_user_567
random_user_567

Reputation: 49

Return all columns for a minimum value depending on a grouped column

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

Answers (2)

Yogesh Sharma
Yogesh Sharma

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

hoangnh
hoangnh

Reputation: 249

You can write sql as below 

SELECT a2.mp, a1.origin, a2.destination
FROM A a1
INNER JOIN
  (SELECT min(price) AS mp,
          destination
   FROM A
   GROUP BY destination) a2 ON a1.destination = a2.destination
AND a1.price = a2.mp

DBFiddle

Upvotes: 1

Related Questions