To get second highest age from the student table

I am trying to get second highest age from the student table.
Student table:
SQL> desc student;
Name Null? Type
----------------------------------------- -------- -----------------
ROLLNO NUMBER
NAME VARCHAR2(20)
AGE NUMBER

And I had tried using following sql:
select age from (select * from student1 order by age )
where rownum = 2
but Iam getting error.

Pls help.

Thanks

Questions by technofreek   answers by technofreek

Showing Answers 1 - 17 of 17 Answers

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

  • Jul 12th, 2011
 

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

Code
  1. SELECT a.name,a.age

  2.  FROM emp a

  3. WHERE 2=

  4. (SELECT count(disinct(b.age))

  5.  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)

  Was this answer useful?  Yes

shylaja

  • Jul 15th, 2011
 

Code
  1. SELECT max(age)

  2. FROM student

  3. WHERE age<(SELECT max(age)

  4. FROM student);

  Was this answer useful?  Yes

Rishana Balkees

  • Jul 18th, 2011
 

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

  Was this answer useful?  Yes

AKSHYA DASH

  • Jul 25th, 2011
 

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

  2. MINUS

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

  Was this answer useful?  Yes

Prabakaran

  • Jul 26th, 2011
 

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

  Was this answer useful?  Yes

anil

  • Aug 17th, 2011
 

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

  Was this answer useful?  Yes

Neelakanta

  • Oct 12th, 2011
 

U can try the following Query-

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

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

Kranthi swaroop

  • Dec 3rd, 2011
 

Try this

Code
  1. SELECT MAX(AGE) ,STUDNAME FROM STUDENT

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

  3. GROUP BY STUDNAME

  4. ORDER BY 1 ASC

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

Santhosh

  • Jan 17th, 2012
 

Code
  1. SELECT min(age) FROM student WHERE age IN (SELECT DISTINCT top 2 age FROM student orderby age DESC)

  Was this answer useful?  Yes

rohitosu

  • Jul 30th, 2012
 

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

  2.   FROM students) a

  3.   WHERE a.r = 2;

  4.  


  Was this answer useful?  Yes

Nazeera Jaffar

  • Sep 30th, 2012
 

Answer

Code
  1. SELECT max(age )

  2. FROM TABLE

  3. WHERE age<(SELECT max(age)

  4.                   FROM TABLE)

  Was this answer useful?  Yes

DEEPU

  • May 16th, 2013
 

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

  Was this answer useful?  Yes

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
  1. 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;

  Was this answer useful?  Yes

Mohan

  • Mar 17th, 2015
 

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

  Was this answer useful?  Yes

Praneeth

  • Apr 2nd, 2015
 

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

  2. WHERE rownum<=2

  3. minus

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

  5. WHERE rownum<= 1

  Was this answer useful?  Yes

Ejaz Ahmed

  • Apr 19th, 2015
 

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

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

Saswati

  • Dec 3rd, 2015
 

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

Code
  1. 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;

  Was this answer useful?  Yes

Neeraj

  • Jan 7th, 2016
 

Code
  1. SELECT max(age)

  2. FROM student

  3. WHERE age < (SELECT max(age)

  4.                 FROM student);

  Was this answer useful?  Yes

Sunil Badam

  • Jan 29th, 2016
 

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

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

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

  3. FROM TABLE.STUDENT)

  4. WHERE RANK=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