Could not get the expected query with SQL Server

Question

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!

Don't group by pv.price. Your sub-query is enough. Fiddle is here — Indi
– Indi, Commented Sep 26, 2013 at 20:31
@Kaf - Alright, it works. But the query result show all the results with gender Female and then show all the results with gender Male I need to to group the the Variations: e.g Title-1 Male Title-1 Female -- Title-2 Male Title-2 Female..... etc — Rodrigo Abib
– Rodrigo Abib, Commented Sep 26, 2013 at 20:39
Add an ORDER clause to put all the Title-1's together. ORDER BY p.title, pv.gender, pv.price — taserian
– taserian, Commented Sep 26, 2013 at 20:44
If the results are correct after removing Group by pv.price you need to get the ordering as you need. Try Order by p.product_id, p.title, pv.gender desc Check this fiddle — Indi
– Indi, Commented Sep 26, 2013 at 20:48

D Stanley · Accepted Answer · 2013-09-26 20:34:50Z

1

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.

answered Sep 26, 2013 at 20:34

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bogdan Sahlean · Accepted Answer · 2013-09-26 20:36:28Z

0

-- 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

answered Sep 26, 2013 at 20:36

Bogdan Sahlean

1

Comments

Laurence · Accepted Answer · 2013-09-26 20:40:22Z

0

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

edited Sep 26, 2013 at 20:40

answered Sep 26, 2013 at 20:35

Laurence

11k1 gold badge28 silver badges36 bronze badges

Collectives™ on Stack Overflow

Could not get the expected query with SQL Server

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related