SQL Server: Limit (MySQL) equal Function in SQL Server
SELECT * FROM MyTable LIMIT 11, 5
1. With Derived Table (Most inefficient way, but applicable to all versions of SQL Server)
FROM ( SELECT *, ROW_NUMBER() OVER ( ORDER BY ColumnName ) AS RowNum
FROM MyTable ) DerivedTable
WHERE RowNum >= 11
ANDRowNum <= 11 + ( 5 – 1 )
2. With CTE Common Table Expression (Applicable to SQL Server 2005/2008/2012)
;
WITH CTE
AS ( SELECT *, ROW_NUMBER() OVER ( ORDER BY ColumnName ) AS RowNum
FROM MyTable)
SELECT *
FROM CTE
WHERE RowNum >= 11
ANDRowNum <= 11 + ( 5 – 1 )
3. With OFFSET_ROW FETCH (Applicable to SQL Server 2012 Only)
Here “OFFSET” means, how many rows to skip, “ROWS FETCH NEXT” means, how many rows to skip
OFFSET 10
ROWS FETCH NEXT 5 ROWS ONLY;
Posted on July 11, 2012, in Sql Server 2005, Sql Server 2008, SQL Server 2012, TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.
Leave a comment
Comments 0