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)
Posted by at

1 comment:

  1. UnknownJanuary 28, 2011 at 10:40 PM

    Yaaa.But I Got Some Other In Mind...

    select MAX(Mark) from Student where Mark <> (Select MAX(Mark) from Student )..

    I Feel It Wl Also suit For This...
    Happy Coding...
    from:
    shareef
    http://sherif-4u4ever.wetpaint.com/

    ReplyDelete

Subscribe to: Post Comments (Atom)