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

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

• 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,

Pravin

• 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

#### 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:

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

#### anand

• May 6th, 2013

```CodeSELECT * 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

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

#### dedeepya

• May 31st, 2017

```CodeData 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;  