how to get top n rows from a table where value of n is passed at run-time? Thanks in advance
views:
35answers:
2
A:
You can use set rowcount. To get the first 100, for example:
declare @myrowcount = 100
set rowcount @myrowcount
select ..... from ... where...order by
since you can use either of:
SET ROWCOUNT { number | @number_var }
davek
2010-02-14 10:47:41
This can give odd results. David Hall's answer will give correct results
gbn
2010-02-14 11:13:22
@gbn: what kind of odd results?
davek
2010-02-14 11:28:34
@Davek: As per BOL: SET ROWCOUNT: "Causes SQL Server to stop processing the query after the specified number of rows are returned." TOP: "Specifies that only the first set of rows will be returned from the query result". Example: http://decipherinfosys.wordpress.com/2007/04/20/set-rowcount-and-table-functions/
gbn
2010-02-14 14:09:05
@gbn - many thanks for the link and example.
davek
2010-02-14 17:04:12
+3
A:
In SQL Server 2005 and beyond you can actually parameterise the top command.
The code below is from MSDN
USE AdventureWorks;
GO
DECLARE @p AS int;
SELECT @p=10
SELECT TOP(@p)*
FROM HumanResources.Employee;
GO
In earlier versions of SQL Server you will need to either use rowcount or dynamic sql.
David Hall
2010-02-14 10:51:49