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

Ankit Singh

Total Post:341

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

    Post:255

    Points:1817
    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

     

      Modified On Apr-11-2018 12:02:48 AM

Answer

NEWSLETTER

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