nth Highest number in a SQL Column

Ashish
Posted by Ashish under Sql Server category on | Views : 3873
This Query Select nth Highest number in a Column .



SELECT MAX(Fieldname) from tablename WHERE Fieldname NOT IN ( SELECT TOP n Fieldname FROM tablename ORDER BY Fieldname DESC )

Set n=0 for Max Row
Set n=1 for Second MAx
Set n=2 for Third max
and so on...

For Ex :
-if you want select First Highest marks get by Student in Class6.
SELECT MAX(Studentname) from table_class6 WHERE Studentname NOT IN ( SELECT TOP 0 Studentname FROM tablename ORDER BY Studentname DESC )

---if you want select Second Highest marks get by Student in Class6.
SELECT MAX(Studentname) from table_class6 WHERE Studentname NOT IN ( SELECT TOP 1 Studentname FROM tablename ORDER BY Studentname DESC )

-if you want select Third Highest marks get by Student in Class6.
SELECT MAX(Studentname) from table_class6 WHERE Studentname NOT IN ( SELECT TOP 2 Studentname FROM tablename ORDER BY Studentname DESC )

---if you want select Fourth Highest marks get by Student in Class6.
SELECT MAX(Studentname) from table_class6 WHERE Studentname NOT IN ( SELECT TOP 3 Studentname FROM tablename ORDER BY Studentname DESC )

Comments or Responses

Login to post response