I will use the Northwind database. You can download the db from this page:
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;
