Write a query to find the second largest value in a given column of a table

Questions by Thiagu1978

Showing Answers 1 - 26 of 26 Answers

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)

veenadevi karumanchi

  • Mar 24th, 2006
 

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

  Was this answer useful?  Yes

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)

  Was this answer useful?  Yes

shashi singh

  • Apr 6th, 2006
 

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

  Was this answer useful?  Yes

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 )

  Was this answer useful?  Yes

jayakumar

  • May 17th, 2006
 

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

  Was this answer useful?  Yes

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.     

 

  

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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

 

  Was this answer useful?  Yes

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;

  Was this answer useful?  Yes

Samuel Edison

  • Mar 27th, 2007
 

Your query is wrong

  Was this answer useful?  Yes

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 )

  Was this answer useful?  Yes

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)

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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
  1. SELECT * FROM table_name a WHERE &n=(SELECT count(DISTINCT(sal)) FROM Table_name b WHERE a.sal<b.sal);

  Was this answer useful?  Yes

anand

  • May 6th, 2013
 


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

  2.  

  Was this answer useful?  Yes

Chetana

  • Jun 18th, 2013
 

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

  Was this answer useful?  Yes

Akhilesh kumar

  • Jun 15th, 2014
 

In SQL-

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

  Was this answer useful?  Yes

AccyTest

  • Nov 28th, 2014
 

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

  Was this answer useful?  Yes

dedeepya

  • May 31st, 2017
 

Code
  1. Data ds;                                                                                                                                

  2. Infile datalines;                                                                                                                      

  3. Input id name$ sal;                                                                                                                    

  4. Datalines;                                                                                                                              

  5. 001 abc 70000                                                                                                                          

  6. 002 def 10000                                                                                                                          

  7. 003 xyz 20000                                                                                                                          

  8. 004 jkl 50000                                                                                                                          

  9. 005 asd 30000                                                                                                                          

  10. ;                                                                                                                                      

  11. Run;

  12.  

  13.  

  14.  

  15.  

  16. Q) In above dataset pick max salary?

  17. Q) In above dataset pick second max salary?

  18. Q) In above dataset pick more than 50000 salary?

  Was this answer useful?  Yes

PUJA

  • Oct 21st, 2017
 

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

  Was this answer useful?  Yes

Give your answer:

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

 

Related Answered Questions

 

Related Open Questions