How to find the 2 minimum salaries in a table?

set rowcount 2

select salary from employee order by salary

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

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.

You guys forgot to add "ORDER BY salary ASC"

Hi,

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

 Thanks,

  Vimal.

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.

The following article can clarify your doubt:

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

select TOP 2 * from emp_salary

order by salary desc....

this is quite simple.....

select top 2 salary from sal order by salary asc;

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

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

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)

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

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

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)

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)

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)

I think you can use below one..

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

This is a more elegant solution:

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)

)

The query is

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