GeekInterview.com
Series: Subject: Topic:
Question: 123 of 192

How to find the two minimum salaries ?

Asked by: Interview Candidate | Asked on: Oct 14th, 2005

Editorial / Best Answer

Answered by: maverickwild

View all answers by maverickwild

Member Since Nov-2005 | Answered On : 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

Answered On : Oct 16th, 2005

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

select min(sal) from emp

  
Login to rate this answer.
mythily

Answered On : Oct 21st, 2005

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

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

Description:

d and n are the objects for the same

  
Login to rate this answer.
Saurabh Kumar

Answered On : 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

Yes  1 User has rated as useful.
  
Login to rate this answer.
Acharya Manoj

Answered On : 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

  
Login to rate this answer.
maverickwild

Answered On : Nov 15th, 2005

View all answers by maverickwild

Try this

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

Yes  5 Users have rated as useful.
  
Login to rate this answer.
l_p_nedunuri

Answered On : Jan 27th, 2006

View all answers by l_p_nedunuri

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

Yes  1 User has rated as useful.
  
Login to rate this answer.

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

Yes  1 User has rated as useful.
  
Login to rate this answer.
Anuj

Answered On : Feb 8th, 2006

SELECT TOP 2 sal FROM emp ORDER BY sal ASC

  
Login to rate this answer.
Mainak Aich

Answered On : 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));

  
Login to rate this answer.
rafeeq uddin ahmed

Answered On : 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)

  
Login to rate this answer.
lakshminarsu

Answered On : 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))

  
Login to rate this answer.
anurag,channa

Answered On : 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

  
Login to rate this answer.
ardsouza

Answered On : May 16th, 2006

View all answers by ardsouza

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.

  
Login to rate this answer.
nileshsingh

Answered On : May 18th, 2006

View all answers by nileshsingh

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

  
Login to rate this answer.
ardsouza

Answered On : May 18th, 2006

View all answers by ardsouza

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

  
Login to rate this answer.
Hanumanth

Answered On : Jun 7th, 2006

We can execute this query to get min two sal

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

  
Login to rate this answer.
anil

Answered On : Jul 27th, 2006

Try this

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

  
Login to rate this answer.
ggk.krishna

Answered On : Apr 17th, 2007

View all answers by ggk.krishna

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.

  
Login to rate this answer.
Mudit Sharma

Answered On : 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

  
Login to rate this answer.


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

  
Login to rate this answer.

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

  
Login to rate this answer.
anupamaraj

Answered On : Mar 3rd, 2008

View all answers by anupamaraj

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.

  
Login to rate this answer.
koolbuzzjj

Answered On : Mar 15th, 2008

View all answers by koolbuzzjj

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

  
Login to rate this answer.

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

  
Login to rate this answer.

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

  
Login to rate this answer.
sabitha.P

Answered On : May 20th, 2008

View all answers by sabitha.P

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

  
Login to rate this answer.

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

  
Login to rate this answer.

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

  
Login to rate this answer.
V dinesh

Answered On : 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)

  
Login to rate this answer.
hiren

Answered On : Jul 25th, 2011

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

  
Login to rate this answer.
kaneolopa

Answered On : Jul 25th, 2011

View all answers by kaneolopa

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

  
Login to rate this answer.
Prabakaran

Answered On : Jul 26th, 2011

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

  
Login to rate this answer.
bizastral-technologies

Answered On : 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 .

  
Login to rate this answer.

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

  
Login to rate this answer.
Rajasekhar Vinjamuri

Answered On : Aug 2nd, 2011

Code
  1. SELECT * FROM(SELECT * FROM emp ORDER BY sal) WHERE rownum<=2

  
Login to rate this answer.
Sam

Answered On : Nov 20th, 2011

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

  
Login to rate this answer.
vamsi krishna devineni

Answered On : Nov 27th, 2011

Code
  1. SELECT * FROM emp e1 WHERE 2>(SELECT count(*) FROM emp e2 WHERE e1.sal>e2.sal);

  
Login to rate this answer.
Kranthi Swaroop

Answered On : Dec 3rd, 2011

Try this Minimum 2 salaries with Employee name

Code
  1. SELECT MIN(EMP),ENAME FROM EMP
  2. WHERE SAL  IN (SELECT DISTINCT MIN(SAL)FROM EMP)
  3. GROUP BY ENAME
  4.  UNION
  5. SELECT MIN(EMP),ENAME FROM EMP
  6. WHERE SAL  IN (SELECT DISTINCT MIN(SAL)FROM EMP WHERE SAL > (SELECT  DISTINCT MIN(SAL) FROM SAL))
  7. GROUP BY ENAME
  8. ORDER BY 1 ASC

  
Login to rate this answer.
GRS.Raja

Answered On : Dec 26th, 2011

Code
  1. SELECT * FROM(SELECT DISTINCT salary FROM emp ORDER BY salary ASC)WHERE rownum<=2;
  2.  
  3. --tab name as emp

  
Login to rate this answer.
Haresh kumar

Answered On : 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)

  
Login to rate this answer.
SUDHEER

Answered On : 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;

  
Login to rate this answer.
mohit

Answered On : Jun 28th, 2012

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

Code
  1. SELECT sal FROM (SELECT * FROM emp ORDER BY sal ASC)
  2. WHERE rownum<=2;

  
Login to rate this answer.
mohit

Answered On : Jun 28th, 2012

If you want to employee name then query is given below

Code
  1. SELECT ename,sal FROM (SELECT * FROM emp ORDER BY sal ASC)
  2. WHERE rownum<=2;

  
Login to rate this answer.
snehal Falke

Answered On : Jul 13th, 2012

Select * FROM emp ORDER BY sal ASC LIMIT 2

  
Login to rate this answer.

Give your answer:

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

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.