Gauri Shankar Arya

• Feb 23rd, 2006

 

To find the second largest salary amount  from employee table

select max(salary) from employees
 where pin < (select max(salary) from employees)

ferozbm

 Profile Answers by ferozbm Questions by ferozbm

• Mar 2nd, 2006

 

select max(sal) from emp_table where sal < (select max(sal) from emp_table)

veenadevi karumanchi

• Mar 24th, 2006

 

select * from emp where score=(select max(score) from emp where score<(select max(score)from emp));

JEELU

• Apr 2nd, 2006

 

  To find Nth largest salary   (not only second largest salary)  , the query is :

 select sal from emp e where &n=(select count(*) from emp

 where e.sal<=sal)

shashi singh

• Apr 6th, 2006

 

Select * from EMP E1 where (N-1)=(Select count(salary) from EMP E2 where salary.E2>salary.E1)

pravinshetty76

 Profile Answers by pravinshetty76 

• Apr 13th, 2006

 

hi feroz,

          You answer is corect. Thanks!!!

Pravin

sharad

• May 12th, 2006

 

To Select Second highest Salary from Emp Table:

Query:  Select Max(Distinct(a.sal)) from emp a  where 2=select (count (distinct(sal)) from emp b where a.sal<b.sal )

jayakumar

• May 17th, 2006

 

select sal(max) form employee where sal >(select sal(max) from employee)

Rajani

• Aug 28th, 2006

 

 

   For any question of this type,.. just follow the simple methodology called   TOP N ANALYSIS.

  For the 3rd highest salary employee,

    Select emp_name , rownum

     From (select emp_name from emp Order by sal DESC)

        where rownum=3.

  For further clarification, just test the similar type of queries with this methodology.     

 

  

sanjeev

• Sep 5th, 2006

 

Feroz's Query is correct. It correctly finds the 2nd largest number in a table.

Can any one plz give the same for nth highest number?

Thanks

Sanjeev Kumar Jha

tvudaykumar

 Profile Answers by tvudaykumar 

• Nov 9th, 2006

 

for nth highest salary

select * from emp e1 where

(select count(distinct(salary) from emp e2

where e2.salary >emp1.salary)=(n-1)

uday

vinod

• Jan 9th, 2007

 

Hi,

select sal from emp e where &n = (select count(*) from emp where e.sal <= sal);

use this query to find the n max sal .

cheers,

vinodkumar

kvinodkannan@gmail.com

 

vinodhsamurai

 Profile Answers by vinodhsamurai 

• Jan 11th, 2007

 

Consider the following table
create table table1(a number);
insert into table1 values(5);
insert into table1 values(23);
insert into table1 values(8);
insert into table1 values(99);
insert into table1 values(99);
commit;
To retrieve the Nth largest number :
========================
Lets say we want to find the 2nd largest number
1)There are two interpretations for the this.
When we consider the distinct values , then 23 is the second largest number.
So  to find the absolute second largest number ,the following query can be used :
select a
from
(
select a , rank() over (order by a desc) rank from
(select distinct(a) from table1)
)
where  rank=2;
2) If we are concerned about true ranking
ie equal values have equal ranks then the following query can be used:(In this case there will be no rank 2)
 select a
 from
 (select a, rank() over (order by a desc) rk from table1)
 where rk=2;

Samuel Edison

• Mar 27th, 2007

 

Your query is wrong

vardhanvatsavayi

 Profile Answers by vardhanvatsavayi 

• May 6th, 2007

 

select sal from emp e where &n = (select count(distinct(sal))  from emp where e.sal <= sal);

Dinesh

• Jun 25th, 2007

 

We can write like this also

select min(Column_Name) from (select top 2 (Column_Name) from Table_Name order by Column_Name ASC )

Rekha

• Aug 23rd, 2007

 

to get the nth highest value in a column:

select MIN(value)
FROM table_name
Where value IN
           (select distinct(optional) TOP N value
             FROM table_name
             ORDER BY value)

akavitha

 Profile Answers by akavitha 

• Feb 27th, 2008

 

Query in DB2 for finding the nth largest value
---------------------------------------------------------
select emp_name,sal from(select emp_name,sal, rownumber() over(order by sal)as rn from emp) a where a.rn=n

siva.reddy.in

 Profile Answers by siva.reddy.in Questions by siva.reddy.in

• Sep 25th, 2012

 

Hi Jayakumar,

The query which you given is wrong please correct it.

Thank you.

siva.reddy.in

 Profile Answers by siva.reddy.in Questions by siva.reddy.in

• Sep 25th, 2012

 

Hi,

To find the nth highest salary and find the second largest value in a given column as you asked both are same. So, both of the above scenarios the below is the query:

Code
SELECT * FROM table_name a WHERE &n=(SELECT count(DISTINCT(sal)) FROM Table_name b WHERE a.sal<b.sal);

anand

• May 6th, 2013

 

Code
SELECT * FROM emp a WHERE (&n-1)=(SELECT count(DISTINCT b.sal) FROM emp b WHERE a.sal<b.sal);


 

Chetana

• Jun 18th, 2013

 

Select MAX(value) from TABLE WHERE value < ( Select MAX(value) from TABLE)

Akhilesh kumar

• Jun 15th, 2014

 

In SQL-

SELECT SAL FROM( SAL,DENSE_RANK() OVER(ORDER BY SAL DESC) RK FROM EMP) WHERE RK=2;

AccyTest

• Nov 28th, 2014

 

Code
SELECT Min(Salary) FROM employee WHERE Salary IN (SELECT Top 2 salary FROM employee ORDER BY salary DESC)
 

dedeepya

• May 31st, 2017

 

Code
Data ds;                                                                                                                                


Infile datalines;                                                                                                                       


Input id name$ sal;                                                                                                                     


Datalines;                                                                                                                              


001 abc 70000                                                                                                                           


002 def 10000                                                                                                                           


003 xyz 20000                                                                                                                           


004 jkl 50000                                                                                                                           


005 asd 30000                                                                                                                           


;                                                                                                                                       


Run;


 


 


 


 


Q) In above dataset pick max salary?


Q) In above dataset pick second max salary?


Q) In above dataset pick more than 50000 salary?

PUJA

• Oct 21st, 2017

 

SELECT * FROM(SELECT DENSE_RANK() OVER (ORDER BY SAL DESC) AS RNK FROM EMPLOYEE) WHERE RNK=2;