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

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

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.

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;

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.

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 )

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 Hope that helps .

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

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

## Answer Question

Click here to Login / Register your free account

## Related Answered Questions

## Related Open Questions