Manoj Thomas
Answered On : Aug 18th, 2006
set rowcount 2
select salary from employee order by salary
Login to rate this answer.
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.
Login to rate this answer.
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.
Login to rate this answer.
sa10
Answered On : Sep 26th, 2006
You guys forgot to add "ORDER BY salary ASC"
Login to rate this answer.
vimal
Answered On : Sep 29th, 2006
Hi,
select min(salary) from employee where salary >(select min(salary) from employee)
Thanks,
Vimal.
Login to rate this answer.
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.
Login to rate this answer.
Jagadish
Answered On : Oct 28th, 2006
The following article can clarify your doubt:
http://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid504195_tax301455,00.html
Login to rate this answer.
Hrushikesh Thite
Answered On : Oct 30th, 2006
select TOP 2 * from emp_salary
order by salary desc....
this is quite simple.....
Login to rate this answer.
ashish gag
Answered On : Feb 19th, 2007
select top 2 salary from sal order by salary asc;
Login to rate this answer.
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....
Login to rate this answer.
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
Login to rate this answer.
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
Login to rate this answer.
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)
Login to rate this answer.
Lakshmi Mandava
Answered On : May 15th, 2007
Will this query works if the table field contains duplicate values?
Login to rate this answer.
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?
Login to rate this answer.
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...)
Login to rate this answer.
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)
Login to rate this answer.
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)
Login to rate this answer.
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)
Login to rate this answer.
I think you can use below one..
select * from (select * from table_name order by asce)
where rownum >=2
Login to rate this answer.
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
Login to rate this answer.
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)
)
Login to rate this answer.
The query is
select max(sal) from emp where sal < (select max(sal) from emp where rownum <3) group by sal
Login to rate this answer.