Find second lowest salary from emp table

aleeza
Profile Answers by aleeza

May 7th, 2008

select min(sal) from emp where sal=(select sal from emp where sal<>MIN(SAL) ) ORselect min(sal) from emp where sal=(select sal from emp having sal<>MIN(SAL) ) May this will help you.I have not tested it in SQL and I am also not sure that the syntax is exactly right .may be you need to do same changes

aleeza
Profile Answers by aleeza

May 9th, 2008

select min(sal) from emp where sal=(select sal from emp where sal<>MIN(SAL) ) OR select min(sal) from emp where sal=(select sal from emp having sal<>MIN(SAL) ) May this will help you.I have not tested it in SQL and I am also not sure that the syntax is exactly right .may be you need to do same changes

ashjain78
Profile Answers by ashjain78

May 22nd, 2008

SELECT a.Emp_Name,a.Emp_sal FROM employee a where 2=(SELECT count(distinct(b.Emp_sal)) FROM employee b WHERE a.Emp_sal<=b.Emp_sal)

priiti_bangarwa26
Profile Answers by priiti_bangarwa26

Jun 2nd, 2008

dont make simple queries the big ones,try to use as simple as possible:-

select max(sal) from emp where sal<>(select max(sal) from emp )

priiti_bangarwa26
Profile Answers by priiti_bangarwa26

Jun 2nd, 2008

dont use that that is for second highest salary

priiti_bangarwa26
Profile Answers by priiti_bangarwa26

Jun 2nd, 2008

sel emp_no,rank(emp_no) from emp order by acct_no asc QUALIFY RANK(emp_no) =2

anantdeshmukh001
Profile Answers by anantdeshmukh001

Jun 6th, 2008

select min(sal) from emp where emp.empcode in(select top 2 empcode from emp order by sal desc)

Go with this query (use inMS SQL server database)
Let me know, it is right or wrong.

ashwin890
Profile Answers by ashwin890

Jul 11th, 2008

select min(salary) from emp where salary in ( select distinct top 2 salary from emp order by salary desc)

anu1985
Profile Answers by anu1985

Jul 17th, 2008

select salary ,empname from emptable order by(salary) limit 1,1;

maartha
Profile Answers by maartha

Jul 21st, 2008

SELECT esalary FROM emp ORDER BY `esalary ` ASC LIMIT 1,1

groovypash
Profile Answers by groovypash

Sep 28th, 2008

select max(salary) from dept where salary in(select distinct top 2 salary from dept order by salary)

Tested query. Change top 2 to top 3 for 3 rd lowest and so on.

ashcarter
Profile Answers by ashcarter

Feb 5th, 2009

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

luzaolai
Profile Answers by luzaolai

Apr 26th, 2009

With t1 as (select firstnme, salary, rank()over(order by salary) as rank from employee)
select * from t1 where rank=2

sivagarlapati
Profile Answers by sivagarlapati Questions by sivagarlapati

Jul 2nd, 2009

select min(salary) from emp where salary!=(select min(salary) from emp)

Try this it's 100% true answer

yogesh.raut
Profile Answers by yogesh.raut

Mar 13th, 2010

SELECT * FROM ( SELECT rownum n, id, salary FROM (select id, salary from employees order by salary )) where n = 2

This works perfectly even if you can find any nth highest or lowest salary.

kirandba
Profile Answers by kirandba

Mar 31st, 2010

Try this

SELECT min(sal) FROM emp WHERE sal > (SELECT Min(sal) FROM emp)

Inner query returns lowest salary. Then get lowest salary excluding the first lowest salary.

yagneswarasankar
Profile Answers by yagneswarasankar

Apr 15th, 2010

select * from (select rownum rank,salary from (select distinct(salary) from employees order by salary asc)) where rank = &n;

Place whatever the number that you want in place of N.

pravintule
Profile Answers by pravintule

May 28th, 2010

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

nisargkothari
Profile Answers by nisargkothari

Sep 16th, 2010

SELECT TOP 1 Empname,Salary FROM (SELECT TOP 2 Empname,Salary FROM Employee ORDER BY Salary ASC) X ORDER BY Salary DESC

ngiridhar903
Profile Answers by ngiridhar903

Oct 22nd, 2010

select * from scott.emp a where 2 = ( select count(distinct(b.sal)) from scott.emp b where a.sal>=b.sal)

tapasgaan
Profile Answers by tapasgaan

Nov 9th, 2010

Select e.salary from emp e

where 1= ( select count(*) from emp s

where s.salary<e.salary)

pravesh28
Profile Answers by pravesh28 Questions by pravesh28

Dec 28th, 2010

there r various query for this question

1 select min(salary)
      from emp
      where salary in ( select top 2 salary
                                   from emp
                                    order by salary desc);

mkaleelmd
Profile Answers by mkaleelmd

Apr 7th, 2011

SELECT * FROM
            (SELECT
                  employee_id
                  ,salary
                 ,dense_rank() OVER ( ORDER BY salary) as ranking
             FROM employees) a
WHERE a.ranking=2

jackiezhou
Profile Answers by jackiezhou

Apr 12th, 2011

select top(1) Sales
from dbo.Sales
where EmpId <> (
select top(1) EmpId from dbo.Sales order by Sales
)
order by Sales

abhishekrc2007
Profile Answers by abhishekrc2007

Apr 16th, 2011

select salary from emp_table order by salary asc limit 1,1;

Pallavi g rao
Profile Answers by Pallavi g rao

Apr 21st, 2011

select * from emp a where 2=(select count(distinct(b.sal))from emp b where a.sal>=b.sal)

chaitanya_m03
Profile Answers by chaitanya_m03

May 19th, 2011

Select * From (Select * From sample_count_ex Where salary < (Select salary From (Select * From sample_count_ex Order By 4 Desc) Where rownum = 1) Order By 4 Desc) Where rownum = 1;

First query Select * From sample_count_ex Order By 4 Desc gets salary in descending order as we r doing order by desc;

So (Select salary From (Select * From sample_count_ex Order By 4 Desc) Where rownum = 1) gets Highest salary in that table

and (Select * From sample_count_ex Where salary < (Select salary From (Select * From sample_count_ex Order By 4 Desc) Where rownum = 1) Order By 4 Desc) gets Second highest Salary

and since rownum= 1

it returns only Second highest salary every time

mukes72
Profile Answers by mukes72

May 27th, 2011

select min(salary) from emp_table<>(select min(salary) from emp_table
);

ziaur10
Profile Answers by ziaur10

Jun 1st, 2011

Sorry Previous is not good but this one seems good.

Thanks
Ziaur
---------------------------------------------------------------------------------------

--USE [MyDb]
GO
-- Clear all
drop table [dbo].[Student]
GO
/****** Object: Table [dbo].[Student] Script Date: 06/01/2011 18:40:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Student](
[Name] [varchar](255) NULL,
[Number] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Student] ([Name], [Number]) VALUES (N's1', 57)
INSERT [dbo].[Student] ([Name], [Number]) VALUES (N's2', 60)
INSERT [dbo].[Student] ([Name], [Number]) VALUES (N's3', 75)
INSERT [dbo].[Student] ([Name], [Number]) VALUES (N's4', 85)
INSERT [dbo].[Student] ([Name], [Number]) VALUES (N's5', 60)
INSERT [dbo].[Student] ([Name], [Number]) VALUES (N's6', 55)

-------------------------------------------------------------
declare @nth int
set @nth = 2
select * from (
select Name, Number, dense_rank() OVER (order by number ) AS 'position'
from Student ) as s
where s.position = @nth

Shaik Kareem
Profile Answers by Shaik Kareem

Jun 6th, 2011

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

yuvaevergreen
Profile Answers by yuvaevergreen

Jun 8th, 2011

sel min(salary) from
(sel salary,rank() over
(order by salary asc) rnk
from emp) a
where a.rnk>1;

sandyl2
Profile Answers by sandyl2 Questions by sandyl2

Jun 22nd, 2011

select sal from(select sal,rank() over (order by sal asc) as rank from emp ) where rank=2;

yeomanie
Profile

Jul 4th, 2011

Select Salary From Empwhere Salary In(Select Salary From Emporder By Salary Asc where Rownum = 2);

Deepak Chandani
Profile Answers by Deepak Chandani

Jul 5th, 2011

SELECT sal FROM
(SELECT sal FROM emp ORDER BY sal DESC)
WHERE rownum=2;

Samanta
Profile Answers by Samanta

Aug 11th, 2011

Select Sal from
(Select sal, rank() over(order by sal desc)rnk
from employee)temp where rnk=2

Ravindra Anugade

Aug 17th, 2011

-- Find Second lowest Salary ----

SELECT DISTINCT(a.sal) FROM emp a WHERE 2 = ( SELECT DISTINCT(COUNT(b.sal)) FROM emp b
WHERE a.sal >= b.sal )

Little Foot

Aug 19th, 2011
Code
 

Areef Oddin Mohammad
Profile Answers by Areef Oddin Mohammad

Aug 19th, 2011
Code
 

Niraj

Mar 22nd, 2012

select max(sal) from emp where sal<>(select max(sal) from emp);

ram

Apr 16th, 2012
Code
 

Gunjan David
Profile Answers by Gunjan David Questions by Gunjan David

May 13th, 2012

Ypu need to know MR. SQL that you cant use = operator with rownum ....