select b.marks from (select distinct marks from student)
a,(select distinct marks from student) b
where a.marks >= b.marks
group by b.marks
having count(b.marks)=3

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

select mark from student s
where 1=(select count(*) from student s1
where s1.mark<s.mark);

try using this...
select top 1 from student where marks in (select top 2 from
student order by marks desc)

select top 1 marks from marks where marks 
<(select max(marks) from marks) order by 1 desc

select top 1 age  from student where age<(select top 1 age  
from student where age<(select distinct max(age) from 
student ))

select top 1 marks from (select top 2 distinct marks from 
students order by marks desc)
order by marks

In Oracle

select marks from
(select marks from
(select marks from students order by marks desc)
where rownum<3 
order by marks asc)
where rownum<2

select max(marks) from student where marks not in (select 
max(marks) from student)

select a.marks from student a where(select count(b.marks)
from student b where a.marks<=b.marks)=2

SELECT TOP 1 Marks (SELECT TOP 2 Marks from Tablename Order 
by Marks Desc)From Tablename order by Marks Asc

select max(mark) from student where mark not in (select max
(mark)  from student)

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

SELECT * FROM(
SELECT ROW_NUMBER() OVER (ORDER BY mark) AS Stu_Rank
FROM student) As T
WHERE Stu_Rank = 2

4th highest salary 

SELECT * FROM(
			SELECT ROW_NUMBER() OVER (ORDER BY 
grosssalary desc) AS salaryno  , * 
			FROM (
				select distinct grosssalary 
from salarydetails where inmon='dec' and inyear = 2007
				) as a
			) As T
WHERE t.salaryno = 4

3rd lowest salary

SELECT * FROM(
			SELECT ROW_NUMBER() OVER (ORDER BY 
grosssalary ) AS salaryno  , * 
			FROM (
				select distinct grosssalary 
from salarydetails where inmon='dec' and inyear = 2007
				) as a
			) As T
WHERE t.salaryno = 3

This ans. particularly when you want 2nd highest marks?
and not for nth highest marks.

SELECT MAX(mark) FROM student WHERE mark <
(SELECT MAX(mark)FROM student)

with temptab as
(
select row_number() over(order by substring(marks,1,2)) as 
rownum,* from Student
)
select * from temptab where rownum=1

SELECT TOP (1) stuMark 
FROM Student 
WHERE stuMark < (SELECT MAX(stuMark) FROM Student)
ORDER BY stuMark DESC

Select max(Marks) from Marks where Marks <
(select max(Marks)from Marks)

select distinct mark from student_mark s
where (select count(distinct s1.mark) from student_mark s1
where s1.mark>=s.mark )=4;

I have student marks in a student table. I need all record
where second  highest mark .Then what will the query for this?

SELECT * FROM `student` WHERE class='Six' ORDER BY mark DESC
LIMIT 1,1

create table test(id int identity,marks int)
insert into test
select 20
union all
select 31
union all
select 33
union all
select 1
union all
select 3
union all
select 100
union all
select 88

select * from test


with data as
(
select marks,row_number() over(order by marks desc) as rno
from test  
)
select * from data where rno = 3