Sql query to fetch 3rd lowest value

I have a table called employee_salary, having two columns(emp_id, emp_salary) in it.

I have a requireme开发者_开发技巧nt to fetch 3rd lowest emp_salary from this table. In this case, what should be my query so that i can get the exact value.

I have tested this in Postgres Database. I hope this query work in all type of database. please try this.

SELECT 
  [emp_salary]
FROM [employee_salary]
GROUP BY [emp_salary]
ORDER BY [emp_salary] LIMIT 1 OFFSET 2;

This may be one solution

select top 1 * from
(
    select top 3 * from
    (
     select distinct  emp_sal from employee order by asc emp_sal
    ) d orderby desc emp_sal
)

SELECT TOP 1 * FROM employee_salary WHERE emp_salary in (SELECT TOP 3 emp_salary FROM employee_salary ORDER BY emp_salary) ORDER BY emp_salary DESC

However, this does not work in all DBs. You need to find out alternative. For eg. in Informix, the statement will be SELECT FIRST 1 *

Using windowing functions... this construct is for SQL Server:

;WITH CTE AS
(
SELECT ..., ROW_NUMBER() OVER (ORDER BY emp_salary) AS rn
FROM myTable
)
SELECT ...
FROM CTE
WHERE rn = 3

for identical salaries you can get using RANK () function in SQL Server

;WITH CTE AS
(
SELECT ..., RANK() OVER (ORDER BY emp_salary) AS rn
FROM myTable
)
SELECT ...
FROM CTE
WHERE rn = 3

I got the answer by executing the following query in sql server 2008

Select MIN(emp_salary) from MyTable Where emp_salary in 
(Select DISTINCT TOP 3 emp_salary from MyTable order by 1 DESC)

I got the 3rd minimum value.

DISTINCT is used to when one or more salary are same.

select * from table_name where col_name = (select (min(col_name) from table_name where col_name > (select min(col_name) from table_name where col_name > (select min(col_name) from table_name)));

You need 3rd lowest Salary. Let dive deep in the question. 1st requirement is Need Salary , 2nd requirement is need lowest salary and 3rd requirement is 3rd Lowest Salary

SELECT * FROM employee_salary // "It will give us Salary"
ORDER BY emp_salary DESC  //"It will show lowest salary on top"
LIMIT 2,1   // As wee need 3rd salary , i am saying LIMIT 2,1 skip first 2 and show 3rd one