Wednesday, March 31, 2010

How to find second Maximum and Minimum of a table

To Find the 2nd Maximum Of Mark in a Table
SELECT * FROM Student a WHERE 2=(SELECT count(DISTINCT Mark)
FROM Student b WHERE a.Mark<=b.Mark)
To Find the 2nd MinimumOf Mark in a Table
SELECT * FROM Student a WHERE 2=(SELECT count(DISTINCT Mark) FROM Student b WHERE a.Mark>=b.Mark)
Find nth Maximum
SELECT * FROM Student a WHERE n=(SELECT count(DISTINCT Mark)
FROM Student b WHERE a.Mark<=b.Mark)
Find nth Minimum
SELECT * FROM Student a WHERE n=(SELECT count(DISTINCT Mark) FROM Student b WHERE a.Mark>=b.Mark)