• Marek Vavrovic

How to list the most expensive price of items by category in MS SQL Server.

I will use the Northwind database. You can download the db from this page:

https://parallelcodes.com/download-sql-northwind-database-bak-file/


Simple database diagram:


Example 1: Using the subquery.


use Northwind;

go


--this will return the products whose prices match the most expensive products by category


Select CategoryID,ProductName,UnitPrice

from Products p1

where UnitPrice in (select MAX(UnitPrice) from Products p2

where p1.CategoryID = p2.CategoryID

)

order by UnitPrice desc


--Query to retrieve the most expensive unit price per category


Select CategoryID,MAX(unitprice) Max_Unit_Price_by_category from Products

group by CategoryID

order by Max_Unit_Price_by_category desc


Example 2: using join.


Select CategoryID,ProductName,UnitPrice

from Products p1

inner join

(

Select CategoryID as CatID,

MAX(unitprice) Max_Unit_Price_by_category

from Products

group by CategoryID

)p2

ON p1.CategoryID=p2.CatID

AND p1.UnitPrice=p2.Max_Unit_Price_by_category

Order by UnitPrice desc



Example 3: using ROW_NUMBER()


Select * from

(

Select CategoryID,ProductName,UnitPrice,

ROW_NUMBER() OVER(PARTITION BY CategoryID ORDER BY UnitPrice DESC) _Rank

from Products

)MostExpensiveProducts

Where _Rank =1




Example 4: using CTE and Rank() function


With Top_Products as

(

Select CategoryID,ProductName,UnitPrice,

RANK() OVER(PARTITION BY CategoryID ORDER BY UnitPrice DESC) _Rank

from Products

)

Select CategoryID,

ProductName,

UnitPrice

from Top_Products

where _Rank=1



Example 5: using CASE statement


Select CategoryId,ProductName,UnitPrice

From

(

Select CategoryID,

ProductName,

UnitPrice,

CASE WHEN UnitPrice=

MAX(UnitPrice) OVER(PARTITION BY CategoryID ORDER BY CategoryID)

Then 'TopProduct'

ELSE 'NormalProduct'

END AS Sub_Result

from Products

) Main_Result

Where Sub_Result='TopProduct'



Example 6 : retrieving the most expensive products by category in the snowflake schema

In case to get the most expensive products by category we have to join 3 tables.

Step 1: get the highest price for category. I am using CTE.


With TopCategorie as

(

Select c.ProductCategoryID

,c.Name,

p.ListPrice

from Production.ProductCategory c

inner join

Production.ProductSubcategory s

on c.ProductCategoryID = s.ProductCategoryID

inner join

Production.Product p

on s.ProductSubcategoryID=p.ProductSubcategoryID

)

Select ProductCategoryID,

Name Category,

MAX(ListPrice) Price

from TopCategorie

Group by ProductCategoryID, Name

Order by Price desc



Step 2: using RANK() function to get all the products with the top price


With TopProduct as

(

Select c.ProductCategoryID,

c.Name Category,

s.Name Subcategory,

p.Name Product,

p.ListPrice,

RANK() over (Partition by c.Name order by ListPrice desc) _Rank

from Production.ProductCategory c

inner join Production.ProductSubcategory s

on c.ProductCategoryID = s.ProductCategoryID

inner join

Production.Product p

on s.ProductSubcategoryID=p.ProductSubcategoryID

)

Select *

from TopProduct

where _Rank =1

order by listprice desc;







6 views0 comments

Recent Posts

See All