# How to find the two minimum salaries ?

maverickwild

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

Try this

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

#### chandrakant

• Oct 16th, 2005

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

select min(sal) from emp

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

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

#### maverickwild Profile Answers by maverickwild

• Nov 15th, 2005

Try this

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

#### l_p_nedunuri Profile Answers by l_p_nedunuri

• Jan 27th, 2006

Select min(sal) from emp     union

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

This will return first 2 minimum salaries

#### virendra_050382 Profile Answers by virendra_050382

• Feb 7th, 2006

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

#### Anuj

• Feb 8th, 2006

SELECT TOP 2 sal FROM emp ORDER BY sal ASC

#### 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));

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

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

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

#### ardsouza Profile Answers by 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.

#### nileshsingh Profile Answers by 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

#### ardsouza Profile Answers by ardsouza

• May 18th, 2006

Correction to my earlier query: The sub query must have count(distinct e2.sal) instead of count(e2.sal)

#### Hanumanth

• Jun 7th, 2006

We can execute this query to get min two sal

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

#### anil

• Jul 27th, 2006

Try this

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

#### ggk.krishna Profile Answers by 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.

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

#### kowmudiswarna Profile Answers by kowmudiswarna Questions by kowmudiswarna

• Sep 25th, 2007

select*from emp where sal=(select sal from(select sal from emp order by sal desc)where rowid<3);

#### ravi214u Profile Answers by ravi214u Questions by ravi214u

• Feb 6th, 2008

select * from emp where sal in (select * from (select min(sal) from emp group by sal order by sal ) where rownum <=2)

#### anupamaraj Profile Answers by 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.

#### koolbuzzjj Profile Answers by 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

#### kalyanchakravarthy.draj Profile Answers by kalyanchakravarthy.draj

• May 5th, 2008

select sal,rownum from emp
where rownum<=2
order by sal;

#### satyam_Ora Profile Answers by satyam_Ora Questions by 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

#### sabitha.P Profile Answers by sabitha.P

• May 20th, 2008

select min(sal) from emp e, dept d
where e.empno=d.empno

#### satyam_Ora Profile Answers by satyam_Ora Questions by 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);

#### subhashree.patra Profile Answers by subhashree.patra Questions by subhashree.patra

• May 28th, 2008

select <sal> from(select <sal> from <tablename> orderby sal asc) where row num<=2

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

#### hiren

• Jul 25th, 2011

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

#### kaneolopa Profile Answers by kaneolopa

• Jul 25th, 2011

SELECT TOP 2 sal, ENO
FROM emp
ORDER BY ENO ASC

#### Prabakaran

• Jul 26th, 2011

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

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

#### amitsrivastava115 Profile Answers by 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

#### Rajasekhar Vinjamuri

• Aug 2nd, 2011

`CodeSELECT * FROM(SELECT * FROM emp ORDER BY sal) WHERE rownum<=2`

#### Sam

• Nov 20th, 2011

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

#### vamsi krishna devineni

• Nov 27th, 2011

`CodeSELECT * FROM emp e1 WHERE 2>(SELECT count(*) FROM emp e2 WHERE e1.sal>e2.sal);`

#### Kranthi Swaroop

• Dec 3rd, 2011

Try this

Minimum 2 salaries with Employee name

```CodeSELECT MIN(EMP),ENAME FROM EMP
WHERE SAL  IN (SELECT DISTINCT MIN(SAL)FROM EMP)
GROUP BY ENAME
UNION
SELECT MIN(EMP),ENAME FROM EMP
WHERE SAL  IN (SELECT DISTINCT MIN(SAL)FROM EMP WHERE SAL > (SELECT  DISTINCT MIN(SAL) FROM SAL))
GROUP BY ENAME
ORDER BY 1 ASC```

#### GRS.Raja

• Dec 26th, 2011

```CodeSELECT * FROM(SELECT DISTINCT salary FROM emp ORDER BY salary ASC)WHERE rownum<=2;

--tab name as emp```

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

#### 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;

#### mohit

• Jun 28th, 2012

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

```CodeSELECT sal FROM (SELECT * FROM emp ORDER BY sal ASC)
WHERE rownum<=2;```

#### mohit

• Jun 28th, 2012

If you want to employee name then query is given below

```CodeSELECT ename,sal FROM (SELECT * FROM emp ORDER BY sal ASC)
WHERE rownum<=2;```

#### snehal Falke

• Jul 13th, 2012

Select * FROM emp ORDER BY sal ASC LIMIT 2

Send   Reset