CODEWITHSUNDEEP
sql
Kamal
Kamal

Reputation: 63

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 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.

Upvotes: 3

Views: 10457

Answers (8)

Mr. Black
Mr. Black

Reputation: 12082

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;

Upvotes: 1

Junaid Javed
Junaid Javed

Reputation: 92

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

Upvotes: 0

JC mixer
JC mixer

Reputation: 1

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

Upvotes: 0

Arun Kumar
Arun Kumar

Reputation: 59

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.

Upvotes: 0

Pankaj Agarwal
Pankaj Agarwal

Reputation: 11311

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

Upvotes: 1

gbn
gbn

Reputation: 432210

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

Upvotes: 0

Mayank
Mayank

Reputation: 5728

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 *

Upvotes: 0

Pranay Rana
Pranay Rana

Reputation: 176896

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
)

Upvotes: 1

Related Questions