10

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 :)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Brenelyn
  • 593
  • 4
  • 8
  • 15

2 Answers2

22

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

Fabian Bigler
  • 10,403
  • 6
  • 47
  • 70
  • 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 – Brenelyn Jun 24 '13 at 01:15
  • @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? – Fabian Bigler Jun 24 '13 at 09:11
  • I discussed here want I wanted to do. [link](http://stackoverflow.com/questions/17269078/subquery-returns-more-than-1-value-sql-error-on-multiple-selection) – Brenelyn Jun 25 '13 at 01:52
  • How can we add this output into a temp table? If I append `into #temp` after this, i get syntax error. – Ross Cooper Apr 03 '14 at 10:56
  • @RossCooper probably you missed to declare the temp table before the select? create table #temp ( Col1 INT, Col2 INT ) – Fabian Bigler Apr 03 '14 at 11:43
  • syntax error is in "into". While doing into, I don't thinks it's needed to declare a temp table. In fact if you do that, it shows an error. – Ross Cooper Apr 03 '14 at 12:07
9

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Fabian Bigler
  • 10,403
  • 6
  • 47
  • 70