Business Systems Analyst - Clearing - SQL Server - ASP - VB (IL) Next Step Systems US-IL-Chicago Post A Job | Post A Resume

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:

• Retrieving the First N Records from a SQL Query
• Obtaining Ranked Values from a Table

Sean

Read Other SQL Guru Questions

Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article