SQL Server TOP clause

Question

I have some difficult with this clause.

How can I select rows with an interval? I mean like clause limit in MySQL

For example we have this table called "TB":

 id | brand
----|-----
  1 | samsung
  2 | hp
  3 | microsoft
  4 | apple
  5 | sony
  6 | acer
  7 | google
  8 | facebook

I want to select the rows between 3 and 6. In MySQL there is a clause limit wish do that:

select * from TB limit 2,3

The question is how can I do it with clause TOP?

I want to use this clause in a page asp.net wish it display all products by receiving a parameter of page.

How can I do it with SQL Server top clause ?

If you want to have records between 3 and 6 then why TOP clause? can't you achieve this with BETWEEN? Please share sample output. — AK47, Apr 18 '14 at 04:54
i dont want to use it cause it not static ! i dont want to specified by ID column but by ROW ! — Simo, Apr 18 '14 at 04:56
So on which basis you want to apply sort for TOP? Please share sample output. — AK47, Apr 18 '14 at 04:58
im not sure if TOP clause is the right choice, i just want a clause wish display rows by an interval like LIMIT in mysql cause the TOP clause display all from the beginning until the number specified and i want to select on an interval wich is dynamic (wish it gonna be the parametere of page in page aspx who show product by pages) — Simo, Apr 18 '14 at 05:06

score 1 · Accepted Answer · answered Apr 18 '14 at 05:05

As you are using SQL Server 2012, I think you require "Offset & Fetch Next" Please try following

Declare @startFrom int = 5 ---- From which row you want to start
Declare @recCount int = 2  ----- No of records you want to Fetch

select * from myTable
order by id
offset @startFrom rows
fetch next @recCount rows only

SQL Fiddle DEMO

SQL Server TOP clause

1 Answers1