How to find the nth highest Price From Products using Ranking Functions?
Create the Products Table :
CREATE TABLE [dbo].[Products](
Product_Name varchar(50) ,
Price money,
Product_Date date
)
Output :
Product_Name Price Product_Date
A1 20.00 2018-08-03
A8 20.00 2018-08-08
A9 30.00 2018-08-09
A10 10.00 2018-08-10
A12 50.00 2018-08-12
A13 10.00 2018-08-13
A14 80.00 2018-08-14
A15 10.00 2018-08-15
A17 90.00 2018-08-03
A18 10.00 2018-08-03
A19 40.00 2018-08-04
A20 10.00 2018-08-05
Get the Ranking Values Format :
Dense Rank : 1,1,2,3,3,3,4.......etc
Rank : 1,1,3,3,3,6........etc
Row Number :1,2,3,4,5,6,7....etc
Query:
select Price, Rank() over (order by Price ) as [Rank] ,
DENSE_RANK() over (order by Price) as [DENSE_RANK],
ROW_NUMBER() over (order by Price) as [ROW_NUMBER()]
from Products
Output :
Price Rank DENSE_RANK ROW_NUMBER()
10.00 1 1 1
10.00 1 1 2
10.00 1 1 3
10.00 1 1 4
10.00 1 1 5
20.00 6 2 6
20.00 6 2 7
30.00 7 3 8
Find nth Highest Salary using Dense Rank(): ex: n=2
Query:
SELECT distinct Product_Name,Price,Product_Date
FROM(SELECT Product_Name,Price,Product_Date,DENSE_RANK() over(ORDER BY Price desc) AS rk FROM Products) as a where rk = 2
Output :
Product_Name Price Product_Date
A1 80.00 2018-08-03
A8 80.00 2018-08-08
No comments:
Post a Comment