CODEWITHSUNDEEP
sqlsql-serversql-server-2008join
Rodrigo Abib
Rodrigo Abib

Reputation: 83

Could not get the expected query with SQL Server

I´m trying to do a SQL query but I couldn´t get the expected result. I really don´t know what is going wrong.

I have a table Product which contains (product_id, title) and other table Product_Variation which contains (product_variation_id, product_id, description, gender, price)

Basically, I have a product. For each product, have N variations.

e.g

Product: title "I have no idea" Product_Variation: description "T-Shirt", gender "Male", price "59.90"

What I need is select Product and Product_Variation showing only the product with the lowest price.

I don´t care if a product has t-shirt, jacket or anything else as variation. I just need to get the variation which has the lowest price.

My query is:

SELECT b.product_id, b.title, MIN(b.price) as price, b.gender

FROM (

        SELECT p.product_id, p.title, MIN(pv.price) AS price, pv.gender
        FROM products p
        join product_variation pv ON pv.product_id = p.product_id
        GROUP BY p.product_id, p.title, pv.price, pv.gender

    ) b     

GROUP BY b.product_id, b.title, b.price, b.gender

Pls, see my example in SQL Fiddle

Thanks!

Upvotes: 2

Views: 104

Answers (3)

Laurence
Laurence

Reputation: 10976

You can use the rank() window function to order stuff within a partition (group), but giving equal items the same rank:

;With b as (
    Select
        p.product_id,
        p.title, 
        pv.price,
        pv.gender,
        rank() over(partition by p.product_id order by pv.price) rk
    From
        products p
            Inner Join
        product_variation pv
            On pv.product_id = p.product_id
)
Select
  b.product_id, 
  b.title, 
  b.price,
  b.gender
From
  b
Where
  rk = 1

If you only want one per product even if there are equal products, use row_number() instead of rank()

Example Fiddle

Upvotes: 0

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

-- Solution #1
SELECT  *
FROM
(
SELECT  p.product_id, p.title, 
        pv.price, pv.gender,
        ROW_NUMBER() OVER(PARTITION BY p.product_id ORDER BY pv.price) AS row_num
FROM    products p
join    product_variation pv ON pv.product_id = p.product_id
) src
WHERE   src.row_num = 1

-- Solution #2
SELECT  p.product_id, p.title, 
        x.price, x.gender
FROM    products p
cross apply
(
        SELECT  TOP(1) pv.price, pv.gender
        FROM    product_variation pv 
        WHERE   pv.product_id = p.product_id
        ORDER BY pv.price
) x

Upvotes: 0

D Stanley
D Stanley

Reputation: 152654

Since you're using SQL 2008 you can use ROW_NUMBER to find the row with the lowest price:

SELECT *
FROM products p
INNER JOIN 
(SELECT 
    product_id,
    Description,
    Gender,
    Price,
    ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY price)  Row
FROM product_variation )
pv ON pv.product_id = p.product_id
AND Row = 1

If you have two variations with the same price you'll get one random row.

Upvotes: 1

Related Questions