Migrating Top N Queries from Access to SQL Server

Microsoft® SQL Server™ does not have the Microsoft Access syntax for a Top N or Top N Percent query. You can provide this functionality in SQL Server by ordering the output and returning the specified number of rows. The SET ROWCOUNT statement precedes the SELECT statement:

SET ROWCOUNT 5

SELECT qty, ord_date

FROM sales

ORDER BY qty DESC

  

To calculate the percentage of the rows returned, declare a variable. Count the records, compute the number of records that you want, and then select the correct number of records. This query returns the top third of the records:

DECLARE @desiredrows int

SELECT @desiredrows = COUNT(*)/3 FROM sales

SET ROWCOUNT @desiredrows

SELECT qty, ord_date

FROM sales

ORDER BY qty DESC

  

See Also

SET ROWCOUNT

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.