How to find the 2 minimum salaries in a table?

  • Interview Candidate
  • 53
  • 5575

Showing Answers 1 - 53 of 53 Answers

Manoj Thomas

  • Aug 18th, 2006
 

set rowcount 2

select salary from employee order by salary

  Was this answer useful?  Yes

Gorilla Killa

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

  Was this answer useful?  Yes

visweswar

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

  Was this answer useful?  Yes

sa10

  • Sep 26th, 2006
 

You guys forgot to add "ORDER BY salary ASC"

  Was this answer useful?  Yes

vimal

  • Sep 29th, 2006
 

Hi,

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

 Thanks,

  Vimal.

  Was this answer useful?  Yes

gurveen singh rekhi

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

  Was this answer useful?  Yes

Hrushikesh Thite

  • Oct 30th, 2006
 

select TOP 2 * from emp_salary

order by salary desc....

this is quite simple.....

  Was this answer useful?  Yes

ashish gag

  • Feb 19th, 2007
 

select top 2 salary from sal order by salary asc;

  Was this answer useful?  Yes

Shailendra

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

  Was this answer useful?  Yes

amy

  • 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

  Was this answer useful?  Yes

siddhartha_sanyal

  • Mar 29th, 2007
 

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

  Was this answer useful?  Yes

Sybase_Guru

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

  Was this answer useful?  Yes

Lakshmi Mandava

  • May 15th, 2007
 

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

  Was this answer useful?  Yes

Abhijeet

  • 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?

  Was this answer useful?  Yes

Nagaraju Putta

  • Jun 4th, 2007
 

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

  Was this answer useful?  Yes

Abhijit S

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

  Was this answer useful?  Yes

Abhijit S

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

  Was this answer useful?  Yes

nivas4u

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

  Was this answer useful?  Yes

rso2003

  • 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

  Was this answer useful?  Yes

silambazhagan

  • 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 2 salary from emp table in syabse please follow the below query. 

select salary from employee where salary < (

select min(salary) from employee a where 2 =

(select count(*) from employee b where a.salary > b.salary)

)

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions