Find the Nth Maximum Record

Posted: November 22, 2011 in SQL Server 2005

To find the nth maximum record, you can try the following:

-- Create table variable
DECLARE @MyTable TABLE 
       (id       INT NOT NULL IDENTITY, 
        order_no VARCHAR(100), 
        amount   INT);

-- Insert test data
INSERT INTO @MyTable(order_no, amount) VALUES ('Order 1', 10);
INSERT INTO @MyTable(order_no, amount) VALUES ('Order 2', 20);
INSERT INTO @MyTable(order_no, amount) VALUES ('Order 3', 30);
INSERT INTO @MyTable(order_no, amount) VALUES ('Order 4', 40);
INSERT INTO @MyTable(order_no, amount) VALUES ('Order 5', 50);

-- Get the 4th maximum record
SELECT * 
FROM  (SELECT *, (SELECT COUNT(t.amount) 
                  FROM  @MyTable t
                  WHERE t.amount >= t2.amount) RANK 
       FROM   @MyTable t2) t3
WHERE  RANK = 4;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s