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
SELECT a.name,a.age


 FROM emp a 


WHERE 2=


(SELECT count(disinct(b.age))


 FROM emp b WHERE a.age<=b.age);

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
SELECT max(age)


FROM student


WHERE age<(SELECT max(age)


FROM student);

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
SELECT * FROM (SELECT * FOM STUUDENT ORDER BY AGE DESC) WHERE ROWNUM<=2


MINUS


SELECT * FROM (SELECT * FOM STUUDENT ORDER BY AGE DESC) WHERE ROWNUM<=1;

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
SELECT MAX(AGE) ,STUDNAME FROM STUDENT


WHERE AGE  IN (SELECT DISTINCT MAX(AGE)FROM STUDENT WHERE AGE < (SELECT MAX(AGE) FROM STUDENT))


GROUP BY STUDNAME


ORDER BY 1 ASC

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
SELECT min(age) FROM student WHERE age IN (SELECT DISTINCT top 2 age FROM student orderby age DESC)

rohitosu

 Profile Answers by rohitosu 

• Jul 30th, 2012

 

Code
 SELECT * FROM  (SELECT age, dense_rank() OVER ( ORDER BY age DESC)  r 


  FROM students) a 


  WHERE a.r = 2; 


  

manjari_ghosh12

 Profile Answers by manjari_ghosh12 

• Sep 1st, 2012

 

Code
SELECT * FROM (SELECT s.*, rownum age_rank FROM (SELECT * FROM student ORDER BY age DESC) s) WHERE age_rank=2;

Nazeera Jaffar

• Sep 30th, 2012

 

Answer

Code
SELECT max(age )


FROM TABLE


WHERE age<(SELECT max(age)


                  FROM TABLE)

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
SELECT age FROM (SELECT b.age, rownum rm FROM (SELECT DISTINCT(a.age) FROM student a ORDER BY a.age DESC) b WHERE rownum<=2) WHERE rm>=2;
 

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
SELECT age FROM (SELECT DISTINCT(age),rownum  FROM STUDENT ORDER BY AGE DESC)


WHERE rownum<=2


minus


SELECT age FROM (SELECT DISTINCT (age) , rownum  FROM STUDENT ORDER BY AGE DESC)


WHERE rownum<= 1

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
SELECT * FROM (SELECT * FROM STUDENT ORDER BY MARKS DESC) WHERE ROWNUM<=2 MINUS SELECT * FROM (SELECT * FROM STUDENT ORDER BY MARKS DESC) WHERE ROWNUM<=1;

Neeraj

• Jan 7th, 2016

 

Code
SELECT max(age)


FROM student


WHERE age < (SELECT max(age)


                FROM student);

Sunil Badam

• Jan 29th, 2016

 

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

Code
SELECT * FROM (SELECT NAME,ROLLNO,AGE,


RANK() OVER (PARTITION BY STUDENT.ROLLNO ORDER BY STUDENT.AGE DESC)AS RANK


FROM TABLE.STUDENT)


WHERE RANK=2;