I have student marks in a student table. I need second
highest mark .Then what will the query for this?
Answers were Sorted based on User's Feedback
select max(mark) from student where mark <
(select max(mark)from student)
Is This Answer Correct ? | 53 Yes | 14 No |
Answer / madan
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)
Is This Answer Correct ? | 18 Yes | 7 No |
Answer / kumaravel
try using this...
select top 1 from student where marks in (select top 2 from
student order by marks desc)
Is This Answer Correct ? | 16 Yes | 10 No |
Answer / sanjay
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
Is This Answer Correct ? | 4 Yes | 0 No |
Answer / supriya
select max(marks) from student where marks not in (select
max(marks) from student)
Is This Answer Correct ? | 10 Yes | 7 No |
Answer / ravindra singh
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
Is This Answer Correct ? | 9 Yes | 7 No |
Answer / naresh
select top 1 age from student where age<(select top 1 age
from student where age<(select distinct max(age) from
student ))
Is This Answer Correct ? | 3 Yes | 1 No |
Answer / rakesh
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
Is This Answer Correct ? | 3 Yes | 1 No |
Answer / suman
select max(mark) from student where mark <
(select max(mark)from student)
Is This Answer Correct ? | 5 Yes | 3 No |
Answer / siva prakasam
SELECT TOP (1) stuMark
FROM Student
WHERE stuMark < (SELECT MAX(stuMark) FROM Student)
ORDER BY stuMark DESC
Is This Answer Correct ? | 4 Yes | 2 No |
what operator performs pattern matching?
Can select statements be used on views in ms sql server?
How to run sql server 2005 books online on your local system?
What is the use of SCOPE_IDENTITY() function?
what number files will a information contain in SQL Server? How many forms of information files exist in SQL Server? How many of those files can exist for a single database?
What are the built in functions in sql server?
What do you understand by replication in sql server? Mention the different types of replication in sql server.
What is the difference between constraints and triggers?
Explain temporary table vs table variable by using cursor alternative?
Do you know what is recursion? Is it possible for a stored procedure to call itself or recursive stored procedure? How many levels of sp nesting is possible?
Explain the purpose of indexes?
How do I connect to sql server database?