Is any way that I could select specified number of rows in SQL Server? Like on my first query, I wanted to get rows 1-5, then next is rows 6-10, then onwards? Thank you in advance for your answers :)
Add a comment
|
2 Answers
For SQL Server 2005+ (set @startRow and @endRow):
SELECT OrderingColumn
FROM (
SELECT OrderingColumn, ROW_NUMBER() OVER (ORDER BY OrderingColumn) AS RowNum
FROM MyTable
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum BETWEEN @startRow and @endRow
SQL fiddle example: http://sqlfiddle.com/#!3/b4b8c/4
6 Comments
Brenelyn
Hi! Your code works! Thank you very much! But it is possible to do multiple of this in one query? Three different columns with different startrow and endrow just from one table in one query? It looks like this: C1 |C2 |C3 aa |bb |cc dd |ee |ff gg |hh
Fabian Bigler
@Brenelyn No worries. About your question: Why would you to have different start- and endrows? Can you elaborate in another question and then paste the link here, please?
Ross Cooper
How can we add this output into a temp table? If I append
into #temp after this, i get syntax error.Fabian Bigler
@RossCooper probably you missed to declare the temp table before the select? create table #temp ( Col1 INT, Col2 INT )
|
For SQL Server 2012, try this (simply set the offset)
SELECT *
FROM MyTable
ORDER BY OrderingColumn ASC
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY
OFFSET:
Specifies the number of rows to skip before it starts to return rows from the query expression.
FETCH NEXT:
Specifies the number of rows to return after the OFFSET clause has been processed.
Definitions of OFFSET and FETCH NEXT are from here.
Query 1:
Offset 0 => 1-5
Query 2:
Offset 5 => 6-10, etc.
SQL fiddle example: http://sqlfiddle.com/#!6/b4b8c/2
5 Comments
marc_s
SQL = query language, SQL Server = Microsoft RDBMS product
Brenelyn
Thank you very much! Sorry I don't know what to call that so sorry for having it duplicated to other questions :)
Fabian Bigler
@Brenelyn No problem. Just make sure to set the offset according to your current page. Query 1 => Offset 0 Query 2 => Offset 5 etc.
momobo
this is a 2012 feature, right?
Fabian Bigler
@momobo unfortunately, yes this is only for 2012. I also added another answer for SQL 2005 (using row_number)