Clever Geek Handbook

Second maximum value using dense rank function

I have a table like:

name     marks
raja     88
ravi     88
karthik  99
praveen  99
vijay    70

In this table, I am assuming the rank of the table is:

name      marks        rank 
karthirk    99           1
praveen     99           2
raja        88           3
ravi        88           4
vijay       77           5

I get this with a function dense rank()

:

select  name,marks,dense_rank() over(  order by name ) as ranks
from std_D_D order by marks desc

but what I need is from this table above, I need to get the second lowest rank. I want to say what I want:

ravi 88   4

I tried to get the second lowest rank, but I cannot get all the columns. I tried this query:

select max(a.ranks) as b from (
select  name,marks,dense_rank() over(  order by name ) as ranks
from std_D_D ) as a where a.ranks not in
(

select max(a.ranks) as b from (
select  name,marks,dense_rank() over(  order by name ) as ranks
from std_D_D ) as a )
+3


source to share


3 answers


Aswer for my above question:



SELECT * FROM

(SELECT NAME,MARKS,dense_RANK() OVER(ORDER BY RANK DESC,name) AS TEMP_ORDER FROM

(SELECT *,DENSE_RANK() OVER(ORDER BY MARKS DESC,NAME) AS RANK FROM std_D_D) A
) B WHERE TEMP_ORDER=2;
+1


source


You can change the ranking order and choose the second one:



SELECT name, marks
FROM   (SELECT name, makrs, DENSE_RANK() OVER (ORDER BY name DESC) AS ranks
        FROM   std_D_D) t
WHERE  ranks = 2
+2


source


Let me start by saying that if you use DENSE_RANK()

you will get the same rating for people with the same grades, which is not the same as your intended rank.

EDIT

Added an extra based on OP's comment ORDER BY name DESC

.

Sample data

DECLARE @std_D_D table
(
name VARCHAR(10),
marks int
)

insert into @std_D_D values
('raja',    88),
('ravi',    88),
('karthik',  99),
('praveen',  99),
('vijay',    70);

Query

;WITH CTE
AS
(
SELECT name,marks,dense_rank() over(ORDER BY marks ASC,name DESC) as ranks
from @std_D_D
)
SELECT name,marks,ranks
FROM CTE
WHERE ranks = 2

The result of this query will give you

ravi    88  2
0


source






More articles:


All Articles