How To Get nth maximum and minimum value of column from table in sql

Hello,

In my first post i have explain about how to get second highest and lowest value of column from the table.In this post i will
expalin how to get nth highest and lowest value of the column from the table

You have to just replace "TableName" with your table name,
"ColumnName" with your column name and "n" with the highest or lowest value of column[which number of highest or lowest value of column you want]


To find the Nth highest

SELECT * FROM TableName  a WHERE
n = (SELECT count(DISTINCT(b.ColumnName))
FROM TableName b WHERE
a.ColumnName <=b.ColumnName);

To find the Nth lowest

SELECT * FROM  TableName a WHERE
n = (SELECT count(DISTINCT(b.ColumnName))
FROM TableName b WHERE
a.ColumnName >=b.ColumnName);

For example

There is one table "tbl_Employee" which contains Salary column and many other columns

For find the 3th highest salary from "tbl_Employee" Table

SELECT * FROM  tbl_Employee a WHERE  
3 = (SELECT count(DISTINCT
b.Salary)) FROM tbl_Employee b WHERE
a.Salary <=b.Salary);

For find the 5th lowest salary from "tbl_Employee" Table

SELECT * FROM  tbl_Employee a WHERE 
5 = (SELECT count(DISTINCT
b.Salary)) FROM tbl_Employee b WHERE
a.Salary >=b.Salary);