To read the article online, visit http://www.4GuysFromRolla.com/webtech/sqlguru/q022900-1.shtml

The SQL Guru Answers your Questions...


Today's question comes from Shibu T.:

Dear Mr. SQLGURU,
How do I get the second/third/fourth largest from a column of a single table?

Shibu Thomas

Shibu,

In SQL 6.5:

SET ROWCOUNT 5
SELECT foo
FROM  Table
ORDER BY foo DESC

In SQL 7.0:

SELECT TOP 5 foo
FROM  Table
ORDER BY foo DESC

(the 6.5 solution will also work under 7.0)

These queries will return the top X (in this case, 5) largest values of foo. If you're looking for a specific value, say the second largest, then you could use a cursor or a temp table to get to the correct value.

For more information on TOP and ROWCOUNT be sure to check out the following articles here on 4Guys:

You can also learn more about Microsoft SQL Server 2005's ranking functions at Returning Ranked Results with Microsoft SQL Server 2005.

Sean


Article Information
Article Title: SQL Guru: Returning Rows that Contain the Nth Largest Value from a Column
Article Author: Scott Mitchell
Article URL: http://www.4GuysFromRolla.com/webtech/sqlguru/q022900-1.shtml


Copyright 2017 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers