21

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

How do I change my query with LIMIT for SQL-Server?

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

How do I change LIMIT 5,10? Can I use TOP for it?

Mateen Ulhaq
  • 24,552
  • 19
  • 101
  • 135
pretyBoy
  • 213
  • 1
  • 2
  • 5
  • 1
    None of the answers below show the syntax for `SELECT *`, so here it is: `SELECT TOP(5) * FROM [TableName]` –  Jul 18 '17 at 09:04

4 Answers4

30

As of SQL Server 2012, you can write

...
ORDER BY thisColumn, thatColumn
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY
Steve Kass
  • 7,144
  • 20
  • 26
  • 1
    `FETCH NEXT ` is zero indexed so to get the first and only first row do `OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY` – fartwhif Oct 27 '22 at 21:14
25

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

Community
  • 1
  • 1
ChapMic
  • 26,954
  • 1
  • 21
  • 20
6

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
Darren
  • 68,902
  • 24
  • 138
  • 144
2

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
Community
  • 1
  • 1