How to find Nth highest marks in Sql Server  

There are the following ways to find the Nth highest marks.
1. Find Nth highest marks in sql server using a Sub-Query.
2. Find Nth highest marks in sql server using a CTE (Common Table Expression).

0 397 Technology | Sql Server
Tue Dec 25 , 2018

A Sub-Query is a query within a query. It is also known as inner query or nested query. A sub-query is usually added in a where clause of the Sql statement.
CTEs are known as Common Table Expressions. The CTE is preferred to use as an alternative to a Sub-Query/View.


Create table and insert values

Create table Students
(
     Roll_No int primary key identity,
     Name nvarchar(50),																												
     Gender nvarchar(50),
     Marks int
)

Execute

Insert into Students values ('Amy','Male', 95)
Insert into Students values ('Markiana', 'Female', 80)
Insert into Students values ('Steve', 'Male', 70)
Insert into Students values ('Peter', 'Male', 85)
Insert into Students values ('Philip', 'Male', 60)

Execute


Example to find Nth highest marks using Sub-Query

SELECT TOP 1 Marks
FROM (
      SELECT DISTINCT TOP N Marks
      FROM Students 
      ORDER BY Marks DESC
      ) RESULT
ORDER BY Marks

Example to find Nth highest marks using CTE

WITH RESULT AS
(
    SELECT Marks,
           DENSE_RANK() OVER (ORDER BY Marks DESC) AS DENSERANK
    FROM Students
)
SELECT TOP 1 Marks
FROM RESULT
WHERE DENSERANK = N 


Subscribe & Get Regular Updates On Your E-mail