Sql查询获取第3个最低值

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

我有一个名为employee_salary的表,其中包含两列(emp_id,emp_salary)。

I have a requirement 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.

我需要从此表中获取第三低的emp_salary。在这种情况下,应该是我的查询,以便我可以得到确切的值。

6 个解决方案

#1

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

我在Postgres数据库中对此进行了测试。我希望这个查询适用于所有类型的数据库。请试试这个。

select emp_salary from emp_salary group by emp_salary order by emp_salary limit 1 offset 2;

#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
)

#3

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

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 *

但是,这不适用于所有DB。你需要找出替代方案。例如。在Informix中,声明将是SELECT FIRST 1 *

#4

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

使用窗口函数...此构造用于SQL Server:

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

#5

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

对于相同的工资,您可以在SQL Server中使用RANK()函数

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

#6

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

我通过在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.

我得到了第3个最小值。

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

DISTINCT用于当一个或多个薪水相同时。

#1