SQLTeam.com | Weblogs | Forums

Group By in Connection table

I use SQL Sever, and have the "PistolDealer" connection table with the following columns: PistolID, DealerID, Price.
I would like to get a list of dealers that have the cheapest price for each pistol. The list will include all mentioned columns.
Thank You

What have you tried? I would get the Min (Price) by PistolID, then join that back to this table to get the dealers that have that pistol and price. Keep in mind that the same pistol could be sold for different prices, that's why the join includes price


;WITH cte_lowest_prices AS (
    SELECT PistolID, DealerID, Price, 
        ROW_NUMBER() OVER(PARTITION BY PistolID ORDER BY Price, DealerID) AS row_num
    FROM PistolDealer
)
SELECT *
FROM cte_lowest_prices
WHERE row_num = 1