What is the T-SQL equivalent of MySQL syntax LIMIT x, y?

Question

Possible Duplicate:
How to write a (MySQL) “LIMIT” in SQL Server?

How can I change my query with LIMIT Inside for a SQL-Server ?

Code:

SELECT apretiz FROM tableApoint WHERE price = '$newprice' LIMIT 5;

Many things are not working so just asking for help

And how can i change LIMIT 5,10 by example ? Can't I use TOP for it ?

Answer 1

As of SQL Server 2012, you can write

...
ORDER BY thisColumn, thatColumn
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY

Answer 2

As i said it less than one hour ago, you have to use TOP ! (LIMIT is used for MYSQL)

So try to remove LIMIT 5 and do SELECT TOP(5) apretiz.

Also, try to add order by (same reason than before).

Please make a search before asking things. Link to old question

Answer 3

Use the TOP keyword:

 SELECT TOP 5 pretiz 
 FROM tableApoint WHERE price = '$newprice'

Using LIMIT 5, 10 is not part of the SQL Standard and is only available in MySQL.

You could use ROW_NUMBER() for SQL as a temp solution and will get you the same desired output.

SELECT * FROM ( 
  SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM tableApoint 
) a WHERE row >= 5 and row <= 10

Answer 4

SQL Server 2005 and above

If you are using SQL Server 2005 and above, you could use ROW_NUMBER function to assign unique numbers to your rows and then pick the range of values from the output.

Script:

CREATE TABLE table1
(
    textvalue VARCHAR(10) NOT NULL
);

INSERT INTO table1 (textvalue) VALUES
   ('i'),
   ('a'),
   ('e'),
   ('h'),
   ('c'),
   ('l'),
   ('g'),
   ('m'),
   ('d'),
   ('k'),
   ('j'),
   ('f'),
   ('b'),
   ('n');

;WITH letters as
(
    SELECT  textvalue
        ,   ROW_NUMBER() OVER(ORDER BY textvalue) rownum 
    FROM    table1
)
SELECT  textvalue
FROM    letters
WHERE   rownum  BETWEEN 6 AND 10;

Output:

TEXTVALUE
---------
    f
    g
    h
    i
    j