CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlgreatest-n-per-group
ermir
ermir

Reputation: 877

T-Sql select and group by MIN()

I Have 3 tables like:

ProductCategory [1 - m] Product [1-m] ProductPrice

a simple script like this :

select pc.CategoryId ,pp.LanguageId , pp.ProductId ,pp.Price
from ProductCategory as pc
    inner join Product as p on pc.ProductId = p.Id
    inner join ProductPrice as pp on p.Id = pp.ProductId
order by CategoryId , LanguageId , ProductId

shows these tabular data :

CategoryId  LanguageId  ProductId   Price
----------- ----------- ----------- ---------------------------------------
1           1           1           55.00
1           1           2           55.00
1           2           1           66.00
1           2           2           42.00
2           1           3           76.00
2           1           4           32.00
2           2           3           89.00
2           2           4           65.00
4           1           4           32.00
4           1           5           77.00
4           2           4           65.00
4           2           5           85.00

now what I need is: for each category, get full row as is but only with the product that has the minimum price.

I just wrote a simple query that does this like :

with dbData as
(
select pc.CategoryId ,pp.LanguageId , pp.ProductId ,pp.Price
from ProductCategory as pc
    inner join Product as p on pc.ProductId = p.Id
    inner join ProductPrice as pp on p.Id = pp.ProductId
)
select distinct db1.*
from dbData as db1
inner join dbData as db2 on db1.CategoryId = db2.CategoryId
where db1.LanguageId = db2.LanguageId
    and db1.Price = (select Min(Price) 
                        from dbData 
                        where CategoryId = db2.CategoryId
                                and LanguageId = db2.LanguageId)

and its result is correct:

CategoryId  LanguageId  ProductId   Price
----------- ----------- ----------- ---------------------------------------
1           1           1           55.00
1           1           2           55.00
1           2           2           42.00
2           1           4           32.00
2           2           4           65.00
4           1           4           32.00
4           2           4           65.00

Is there a cooler way for doing this ?

Note: The query must be compliant with Sql-Server 2008 R2+

Upvotes: 3

Views: 87

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

You are not using the Product table in your query, so it doesn't seem necessary. I would right this as:

select ppc.*
from (select pc.CategoryId, pp.LanguageId , pp.ProductId, pp.Price,
             row_number() over (partition by pc.CategoryId order by pp.Price) as seqnum
      from ProductCategory pc inner join
           ProductPrice pp
           on pc.ProductId = pp.ProductId
     ) ppc
where seqnum = 1
order by CategoryId, LanguageId, ProductId;

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28860

you can add languageid to partition if you need product prices per categoryid and languageid

select top 1 with ties pc.CategoryId ,pp.LanguageId , pp.ProductId ,pp.Price
    from ProductCategory as pc
    inner join Product as p on pc.ProductId = p.Id
    inner join ProductPrice as pp on p.Id = pp.ProductId
        order by row_number() over  (partition by pc.categoryid order by price)

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 176324

You could use windowed function like RANK():

WITH cte AS 
(
  select pc.CategoryId, pp.LanguageId, pp.ProductId, pp.Price,
    rnk = RANK() OVER(PARTITION BY pc.CategoryId ,pp.LanguageId ORDER BY pp.Price) 
  from ProductCategory as pc
  join Product as p on pc.ProductId = p.Id
  join ProductPrice as pp on p.Id = pp.ProductId
)
SELECT CategoryId, LanguageId, ProductId, Price
FROM cte
WHERE rnk = 1;

LiveDemo

Upvotes: 2

Related Questions