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.

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.

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

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

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

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

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

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

vamsi krishna devineni

Nov 27th, 2011

Code

SELECT*FROM emp e1 WHERE2>(SELECT count(*)FROM emp e2 WHERE e1.sal>e2.sal);

Kranthi Swaroop

Dec 3rd, 2011

Try this

Minimum 2 salaries with Employee name

Code

SELECT MIN(EMP),ENAME FROM EMP

WHERE SAL IN(SELECTDISTINCT MIN(SAL)FROM EMP)

GROUPBY ENAME

UNION

SELECT MIN(EMP),ENAME FROM EMP

WHERE SAL IN(SELECTDISTINCT MIN(SAL)FROM EMP WHERE SAL >(SELECTDISTINCT MIN(SAL)FROM SAL))

GROUPBY ENAME

ORDERBY1ASC

GRS.Raja

Dec 26th, 2011

Code

SELECT*FROM(SELECTDISTINCT salary FROM emp ORDERBY 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

Code

SELECT sal FROM(SELECT*FROM emp ORDERBY sal ASC)

WHERE rownum<=2;

mohit

Jun 28th, 2012

If you want to employee name then query is given below

Code

SELECT ename,sal FROM(SELECT*FROM emp ORDERBY sal ASC)

## How to find the two minimum salaries ?

## Editorial / Best Answer

maverickwildProfile Answers by maverickwild Questions by maverickwild

Try this

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

## Related Answered Questions

## Related Open Questions