There are couple of ways to do it..
here in this blog I have tried with
1) TOP
2) ROWCOUNT
3) ROW_NUMBER()
out of all the three I could prefer to use TOP.
ROWCOUNT will take priority if its specified smaller than TOP.
ROWCOUNT cannot be used in Functions.
ROW_NUMBER() can be used to fetch top N records with partition.
Here We Go..!!
USE AdventureWorks
Using TOP:
SELECT TOP 10 * FROM Person.StateProvince
with variable:
DECLARE @N INT
SET @N = 10
SELECT TOP (@N) * FROM Person.StateProvince
Using ROWCOUNT:
SET ROWCOUNT 10
SELECT * FROM Person.StateProvince
--TO RESET THE ROWCOUNT
SET ROWCOUNT 0
Using ROWNUMBER:
SELECT * FROM
( SELECT ROW_NUMBER()OVER(PARTITION BY 1 ORDER BY NAME) AS ROW_NUM,* FROM
Person.StateProvince
) TAB
WHERE ROW_NUM <=10
ROWCOUNT will take priority if its specified smaller than TOP.
ROWCOUNT cannot be used in Functions.
ROW_NUMBER() can be used to fetch top N records with partition.
Here We Go..!!
USE AdventureWorks
Using TOP:
SELECT TOP 10 * FROM Person.StateProvince
with variable:
DECLARE @N INT
SET @N = 10
SELECT TOP (@N) * FROM Person.StateProvince
Using ROWCOUNT:
SET ROWCOUNT 10
SELECT * FROM Person.StateProvince
--TO RESET THE ROWCOUNT
SET ROWCOUNT 0
Using ROWNUMBER:
SELECT * FROM
( SELECT ROW_NUMBER()OVER(PARTITION BY 1 ORDER BY NAME) AS ROW_NUM,* FROM
Person.StateProvince
) TAB
WHERE ROW_NUM <=10
No comments:
Post a Comment