Madhu Devarasetti

 Profile Answers by Madhu Devarasetti 

• May 31st, 2011

 

you can try this

select max(age) from student where age<(select max(age) from student);

Niveda Durairaj

 Profile Answers by Niveda Durairaj 

• Jun 10th, 2011

 

Select * from (Select name,

                      age,

                      rownum r_num

From  (select name,

              age, 

              rownum r_num

       from student

       order by age desc)

where rownum<=5)

where r_num=2;

try this.....def it will work......my sir taught me......

priyamani

 Profile Answers by priyamani 

• Jul 12th, 2011

 

Try it.. if this does not work, I will post in another way

Code
 

mani

• Jul 13th, 2011

 

select min (age) from (select * from (select distinct age from student order by age desc) where rownum <=2)

shylaja

• Jul 15th, 2011

 

Code
 

Rishana Balkees

• Jul 18th, 2011

 

{geshibot language="sql"}
select max(age)
from student
where
age <(select max(age)
from student){/geshibot}

AKSHYA DASH

• Jul 25th, 2011

 

Code
 

Prabakaran

• Jul 26th, 2011

 

select max(age) from stud where age<(select max(age) from stud)

anil

• Aug 17th, 2011

 

SELECT LEVEL,MAX(AGE)
FROM STUD
WHERE LEVEL=2
CONNECT BY PRIOR
AGE>AGE
GROUP BY LEVEL

Neelakanta

• Oct 12th, 2011

 

U can try the following Query-

select max(age) from student where age < (select max(age) from student)

pavanjarugula

• Oct 25th, 2011

 

I think this will give more better than the previous one.

Select * from std
where age =(select max(age) from emp
where level = &level
connect by prior age > age);

Kranthi swaroop

• Dec 3rd, 2011

 

Try this

Code
 

nagaraj

• Jan 10th, 2012

 

select rollno,name,age from
(select rollno, name,age, dense_rank() over ( partition by null order by age desc ) rnk
from student)
where rnk = 2

Santhosh

• Jan 17th, 2012

 

Code
 

rohitosu

 Profile Answers by rohitosu 

• Jul 30th, 2012

 

Code
 

manjari_ghosh12

 Profile Answers by manjari_ghosh12 

• Sep 1st, 2012

 

Code
 

Nazeera Jaffar

• Sep 30th, 2012

 

Answer

Code
 

DEEPU

• May 16th, 2013

 

SELECT * FROM (SELECT ROLLNO, AGE, ROWNUM AS RN FROM STUDENT) WHERE RN=2

Sujatha N

• May 15th, 2014

 

Try this way:



SELECT MIN(AGE)
FROM (SELECT DISTINCT AGE FROM STUDENT ORDER BY AGE DESC)
WHERE ROWNUM <=2;

you can change the condition value "Where ROWNUM <=2" to get whichever highest age you want to get, like if you need to fetch 4th highest age from the student table, use the below query

SELECT MIN(AGE)
FROM (SELECT DISTINCT AGE FROM STUDENT ORDER BY AGE DESC)
WHERE ROWNUM <=4;

Remember ROWNUM should always associated either with <= or < operator, it will never work with = operator.

Hope this is helpful !!

Abhilash

• Aug 7th, 2014

 

Code
 
 

Mohan

• Mar 17th, 2015

 

SELECT age from (SELECT distinct age,rownum rn from STUDENT ORDER BY AGE DESC)
WHERE RM=2

Praneeth

• Apr 2nd, 2015

 

Code
 

Ejaz Ahmed

• Apr 19th, 2015

 

SELECT MAX(age) FROM table WHERE age <> (SELECT MAX(age) FROM table )

saIKIRAN

• May 10th, 2015

 

asked about the second highest age so as per our common sense (for eg: who are joined in an organization first those persons age will be high when compare to the latest recruited persons )
.............your ans is correct if you remove the word "desc" from your query ....just check it out and i don't know who is this person who liked this query

Saswati

• Dec 3rd, 2015

 

Error in your SQL Query Error : ORA - 00923 You have used FOM instead of FROM

Code
 

Neeraj

• Jan 7th, 2016

 

Code
 

Sunil Badam

• Jan 29th, 2016

 

We can use rank function with partition by.
It is very simple to use.

Code