Series: Subject: Topic:
Question: 71 of 74

# How to find the 2 minimum salaries in a table?

Manoj Thomas

Answered On : Aug 18th, 2006

set rowcount 2

select salary from employee order by salary

Gorilla Killa

Answered On : Sep 1st, 2006

The posted answer in wrong.Correct answer would beset rowcount 2select distinct salary from employee order by salaryThis will take care of any repeats.

visweswar

Answered On : Sep 13th, 2006

May be you only mistaken.

If the Query is for 2 minimum salary rows. Then even the Salary amount can be equal. In that case the 1st answer is correct.

sa10

Answered On : Sep 26th, 2006

You guys forgot to add "ORDER BY salary ASC"

vimal

Answered On : Sep 29th, 2006

Hi,

select min(salary) from employee where salary >(select min(salary) from employee)

Thanks,

Vimal.

gurveen singh rekhi

Answered On : Oct 11th, 2006

Vimal told us the second part .. ( the second minimum salary ) . For the first min its simple :

select salary, min(salary) from employee where salary >(select min(salary) from employee) group by salary.

Answered On : Oct 28th, 2006

The following article can clarify your doubt:

http://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid504195_tax301455,00.html

Hrushikesh Thite

Answered On : Oct 30th, 2006

select TOP 2 * from emp_salary

order by salary desc....

this is quite simple.....

ashish gag

Answered On : Feb 19th, 2007

select top 2 salary from sal order by salary asc;

Shailendra

Answered On : Mar 13th, 2007

hi,
Since you are using the order by clause, please be informed that asc should not be mentioned, as Order by cluase by default sorts the required column  in the descending order....

amy

Answered On : Mar 20th, 2007

In oracle, you should do this to get the 2 minimun salaries:

select salary from (select salary from employee
order by salary asc)
where rownum <= 2

Hi All,

This should work by a simple self join as:

select b.sal
from
emp a, emp b
where
b.sal >= a.sal
group by b.sal
having count(b.sal) = X

Where X = ( if you put 1 : It will be the minimum salary
sly if you put 2 : It will be the second minimum salary

Sybase_Guru

Answered On : Apr 10th, 2007

To get the 2 minimum in a single statement

select b.salary from test a,test b where b.salary>=a.salary group by b.salary
having count(b.salary) in(1,2)

Lakshmi Mandava

Answered On : May 15th, 2007

Will this query works if the table field contains duplicate values?

Abhijeet

Answered On : May 28th, 2007

Vimal told us about finding the 2nd min salary...how about 3rd min will that query work? for finding 3rd,4rth and so on..you need self join?

since we are doing group by this should obviously remove duplicates. So the following query is still correct irrespective of duplicates.

select b.sal
from emp a, emp b
where b.sal > a.sal
group by b.sal
having count(b.sal) in (1,2,3...)

Abhijit S

Answered On : Aug 21st, 2007

The last query works, but only if the values in the operated column are distinct.

For this purpose one needs to make sure that the resultset on which the self join is to be done contains unique values:

Example:

select a.salary, count(a.salary) from (select salary from emp) a ,
(select salary from emp) b
where a.salary > b.salary
group by a.salary
having count(a.salary) in (1,2)

Abhijit S

Answered On : Aug 21st, 2007

Sorry, forgot to mention the 'distinct' keyword in the earlier query.

The query is:

select a.salary, count(a.salary) from (select distinct salary from emp) a , (select distinct salary from emp) b
where a.salary > b.salary
group by a.salary
having count(a.salary) in (1,2)

nivas4u

Answered On : Sep 28th, 2007

Above query returns 2nd min and 3rd min as forget to put "=" in query. Below query returns correct result event it have duplicate salary records.

select a.salary
from (select distinct salary from test) a ,
(select distinct salary from test) b
where a.salary >= b.salary
group by a.salary
having count(a.salary) in (1,2)

gomzy007

Answered On : Jun 16th, 2008

I think you can use below one..

select * from (select * from table_name order by asce)
where rownum >=2

rso2003

Answered On : Sep 14th, 2008

This is a more elegant solution:

select ee.name, ee.salary from employee ee
where 2<(select count(*) from employee e
where ee.salary< e.salary)

This will give you exactly the two minimum salaries in the table.

Thanks
rso2003

silambazhagan

Answered On : Jan 2nd, 2009

Top function is only working in sql server, please ignore it.
if you include any clolumn with max() function it will not give proper output.

guys, if you want to need minimum 2salary from emp table in syabse pleasefollowthe below query.

selectsalary fromemployee wheresalary < (

select min(salary) from employee a where 2 =

(select count(*) fromemployee b where a.salary > b.salary)

)

gcvpgeek

Answered On : Mar 4th, 2009

The query is

select max(sal) from emp where sal < (select max(sal) from emp where rownum <3) group by sal