to retrieve second highest value

[daniel_5191]

Admin ,
Sorry , I am newbie in SQL
I wish to retrieve second highest value in a table using SQL statement .

SELECT MIN ( E.marks )
FROM ( SELECT TOP 2 E.marks FROM exam E ORDER BY marks DESC ) exam

Does my coding has any mistake ?

only MS SQL support TOP ? what about MySQL / Access / Sysbase ?


What if i want to retrieve the value in SQL using Access only ? How to code for the SQL statement using Access ?

Thank you for your solution in advanced .

[administrator]

Something like this should work in SQL Server (you might have to tweak it a little, it's not tested):

SELECT MIN ( MaxMarks ) FROM ( SELECT TOP 2 E.marks As MaxMarks FROM exam E ORDER BY marks DESC )

Only SQL Server has the TOP keyword, but in MySQL you can use the LIMIT keyword instead. In Access you can only order the marks descending and then get the second result in your program...


Cheers,
_________________
Peter
ASP & ASP.NET Articles and Tutorials