Monday, 27 August 2018

How to find the nth highest Price From Products using Ranking Functions?


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

Top Agile Interview Questions & Answers

Top Agile Interview Questions & Answers 1. What is Agile Testing? The first question of agile interview question tests your k...