How to find the two minimum salaries ?

  • Interview Candidate
  • 44
  • 21840

Editorial / Best Answer

maverickwild  

  • Member Since Nov-2005 | Nov 15th, 2005

Try this

select sal from (select * from order by sal asc) where rownum < 3

Showing Answers 1 - 44 of 44 Answers

chandrakant

  • Oct 16th, 2005
 

To select min sal from a table we should use this query

select min(sal) from emp

  Was this answer useful?  Yes

mythily

  • Oct 21st, 2005
 

To find the two minimum salaries among table, we have to use the Nested queries.

> select d.*, min(sal) from <table_name> d< (select n.*,min(sal) from <table_name> n)

Description:

d and n are the objects for the same <Table_Name>

  Was this answer useful?  Yes

Saurabh Kumar

  • Oct 21st, 2005
 

Try This

Select min(salary) from < Table Name > where Salary>(Select min(salary) from < Table Name >)

throug which you get second minimum salary

Acharya Manoj

  • Oct 25th, 2005
 

Following SQL returns nth ranked salary from emp table

select * from (select rownum rn,sal from (select distinct sal From emp order by sal desc)) where rn = 13

by changing the = sign to < will return the top n ranked salaries from emp

select * from (select rownum rn,sal from (select distinct sal From emp order by sal desc)) where rn < 13

  Was this answer useful?  Yes

Anuj

  • Feb 8th, 2006
 

SELECT TOP 2 sal FROM emp ORDER BY sal ASC

  Was this answer useful?  Yes

Mainak Aich

  • Feb 28th, 2006
 

I executed the sql posted by Anuj but it was giving error.

Anyway I'm giving an alternative query which can give the two minimum salary from the table emp.

select min(sal) from emp union select min(sal) from (select sal from emp where sal>(select min(sal) from emp));

  Was this answer useful?  Yes

rafeeq uddin ahmed

  • Apr 11th, 2006
 

This query is on authors table in pubs database ,which return the second min(zip)

select min(zip) from authors where zip !=(select min(zip) from authors)

  Was this answer useful?  Yes

lakshminarsu

  • Apr 14th, 2006
 

Suppose if the table name is emp with the fields num,salary then the following query gets the two minimum salaries.

select salary from emp where salary<=(select min(salary) from emp where salary>(select min(salary) from emp))

  Was this answer useful?  Yes

anurag,channa

  • Apr 22nd, 2006
 

select sal as min_sal from(select sal from employees order by sal)where rownum<=2;this one is correct try out

  Was this answer useful?  Yes

ardsouza

  • May 16th, 2006
 

Use of inline queries (and more so the use of 'ORDER BY' in inline queries) is not supported in earlier versions of Oracle RDBMS. This can be done using a sub-query.

select distinct e1.sal

from emp e1

where 13 >= (select count(e2.sal)

from emp e2

where e2.sal > e1.sal)

order by 1 desc

This fetches the top 13 salaries.

  Was this answer useful?  Yes

nileshsingh

  • May 18th, 2006
 

If we are required to find 1st two minimum sal then we can simply execute the following query:

select sal from (select sal from  order by sal) where rownum < 3;

now if we are suppose to find the two min sal among the table then the query can be:

select min(sal) from  union

select min(sal) from

  Was this answer useful?  Yes

Hanumanth

  • Jun 7th, 2006
 

We can execute this query to get min two sal

 select * from emp  where sal =(select min(sal) from emp )

  Was this answer useful?  Yes

anil

  • Jul 27th, 2006
 

Try this

select * from emp e where 2>=(select count(*) from emp c where e.sal >=c.sal)

  Was this answer useful?  Yes

ggk.krishna

  • Apr 17th, 2007
 

Hi, the following query gives you the o/p what you wanted.

SELECT SAL FROM ( SELECT DISTINCT SAL FROM EMP ORDER BY SAL) WHERE ROWNUM<3

I hope this will work.

Thanks & Regards,
G Gopi Krishna.

  Was this answer useful?  Yes

Mudit Sharma

  • Aug 2nd, 2007
 

There is very simple query for this:

select sal from (select sal from <Table_Name order by sal>)
where rownum <= 2;

This query will give the two minimum salary paid.

Regards
Mudit

  Was this answer useful?  Yes

anupamaraj

  • Mar 3rd, 2008
 

declare @x integer
declare @y integer
select @x=min(salary) from test_dept where salary > (select min(salary) from test_dept)
select @y=min(salary) from test_dept
print @y
print @x

Output will be two distinct minimum values.

If you use
select top 2 salary from test_dept order by salary asc
then the two minimum values wont be distinct. If there are duplicates of minimum salary, then those two will show up.

So the former method is better.

  Was this answer useful?  Yes

koolbuzzjj

  • Mar 15th, 2008
 

Try this to get the two minimun salary
select min(salary) from emp union select min(salary) from emp where salary> (select min(salary) from emp )

Try this to get the second minimum salary
select min(salary) from emp where salary> (select min(salary) from emp )


With Regards
Jinu

  Was this answer useful?  Yes

satyam_Ora

  • May 16th, 2008
 

Suppose we have table name emp having below records:
SQL> select * from emp;

ENAME             ENO     SALARY
---------- ---------- ----------
Satyam              1      18000
Abhishek            2      15000
Monica              3      22000
Jayaram             4      19000

Now we need to find the two mnimum salaries among table?
SQL> select * from emp where salary in((select min(salary) from emp),(select min(salary) from emp where salary > (select min(salary) from emp)));


ENAME             ENO     SALARY
---------- ---------- ----------
Satyam              1      18000
Abhishek            2      15000

  Was this answer useful?  Yes

satyam_Ora

  • May 25th, 2008
 

The below will suerly work:

To find 2nd min salary:
sql>select min(salary) from emp where salary>(select min(salary) from emp);

To find two minimum salary:
sql> select * from (select distinct salary from emp order by salary asc) where rownum<3;
sql> select min(salary) from emp union select min(salary) from emp where salary>(select min(salary) from emp);

  Was this answer useful?  Yes

V dinesh

  • Jul 23rd, 2011
 

select * from (
select salary,
row_number() over (partition by 1 order by salary ) Num
from salary_table
group by salary )
where No = &no (1 means lowest sal,,2 means 2nd lowest sal,,3rd means 3rd lowest)

  Was this answer useful?  Yes

hiren

  • Jul 25th, 2011
 

row number 0,1,2 are arrange to order by and retrieve the query

  Was this answer useful?  Yes

Prabakaran

  • Jul 26th, 2011
 

select min(sal) from emp where sal>(select min(sal) from emp)

  Was this answer useful?  Yes

bizastral-technologies

  • Aug 2nd, 2011
 

The query that i am giving you below will not only give you two minimum salaries but 2 distinct minimum salaries, if say 2 people have salary as 900, it will not include 900 twice , also by using desc in order by you can use it to find not only top n but distinct top n salaries that many people fail to emphasize on

select distinct(e.sal),rownum as "rank" from (select distinct(m.sal) from emp m order by m.sal ) e
where rownum <=2

Hope that helps .

  Was this answer useful?  Yes

amitsrivastava115

  • Aug 2nd, 2011
 

The below query will not only give you 2 minimum salaries but also give you 2 distinct minimum salaries, i.e if the last 2 min salaries are 800 it will not repeat 800. With the use of desc you can also find top n salaries that are distinct which a lot of people ignore

select distinct(e.sal),rownum as "rank" from (select distinct(m.sal) from emp m order by m.sal ) e
where rownum <=2

Hope this helps.

Regards

  Was this answer useful?  Yes

Rajasekhar Vinjamuri

  • Aug 2nd, 2011
 

Code
  1.  

  Was this answer useful?  Yes

Sam

  • Nov 20th, 2011
 

select * from (select * from emp order by sal) where rownum<=2;

  Was this answer useful?  Yes

vamsi krishna devineni

  • Nov 27th, 2011
 

Code
  1.  

  Was this answer useful?  Yes

Kranthi Swaroop

  • Dec 3rd, 2011
 

Try this

Minimum 2 salaries with Employee name

Code
  1.  


  Was this answer useful?  Yes

GRS.Raja

  • Dec 26th, 2011
 

Code
  1. span style="font-style: italic;">--tab name as emp

  Was this answer useful?  Yes

Haresh kumar

  • Jan 3rd, 2012
 

To find out any(1st,2d,3d,....) minimum/maximum salary
you can use either sub query or co-related sub query..
using sub query

select min(sal) from emp where sal <(select min(sal) from emp);(gives 2nd minimum salary).
select min(sal) from emp where sal <(select min(sal) from emp where sal < (select min(sal) from emp)); (3rd)
(similar is true for maxsal)
using co-related sub query
select distinct a.sal from emp a where &n = (select distinct(count b.sal) from emp b where a.sal <=b.sal);
in co-related sub query you are actually comparing values of your main query i.e(a.sal) with (b.sal)
so ,
your sub query has a relationship whit main query .(sub query in not independent)

  Was this answer useful?  Yes

SUDHEER

  • May 24th, 2012
 

First arrange the student ages from student table in the descending order then query with row number condition:

select * from (select rownum as a ,age from student order by age desc)
where a=2;

  Was this answer useful?  Yes

mohit

  • Jun 28th, 2012
 

Here we are talking about two minimum salary not for 2nd minimum salary

Code
  1.  

  Was this answer useful?  Yes

mohit

  • Jun 28th, 2012
 

If you want to employee name then query is given below

Code
  1.  

  Was this answer useful?  Yes

snehal Falke

  • Jul 13th, 2012
 

Select * FROM emp ORDER BY sal ASC LIMIT 2

  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