HOW TO FIND THE NTH MAXIMUM AND MINIMUM VALUE IN SQLSERVER.

Ankit Singh

Total Post:341

Points:2389
Posted by  Ankit Singh
 521  View(s)
Ratings:
Rate this:
I want to To find the Nth maximum and minimum value in sqlserver.
  1. aditya kumar Patel

    Post:254

    Points:1810
    Re: How To Find the Nth Maximum and Minimum Value in Sqlserver.

    DECLARE @table TABLE(ID integer,AMOUNT integer)

    INSERT INTO @table values(1, 5000)

    INSERT INTO @table values(2, 200)

    INSERT INTO @table values(3, 4500)

    INSERT INTO @table values(4, 3000)

    INSERT INTO @table values(5, 3200)

    INSERT INTO @table values(6, 3100)

    INSERT INTO @table values(7, 150)

    SELECT ID, AMOUNT

    FROM

    (

    SELECT ID, AMOUNT, Row_Number() OVER(ORDER BY AMOUNT DESC) AS highest

    FROM @table

    ) as x

    WHERE highest = 1

    SELECT id, AMOUNT

    FROM

    (

    SELECT id, amount, Row_Number() OVER(ORDER BY AMOUNT ASC) AS lowest

    FROM @table

     

    ) as x

    WHERE lowest =1

     

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!