LIMIT

• scottsanpedro

  SSCrazy

  Points: 2130

  More actions

  September 10, 2004 at 4:23 am

  #59420

  Hi,

  My company has brought some Cart Manager software that in its original state uses CSV files as the back end.

  We have since converted to SQL Server 2000.

  Part of the code to display 1 of 30 books <<next>> <<Prev>> etc is to use an statment in SQL ANSI (whatever) called LIMIT

  Is there a way of replicating the LIMIT SQL function in SQL Server.

  -------This from the Vendor---------

  If you're using Microsoft SQL server for data storage, this is a problem with the db - not CCP. This is strange to me, but Microsoft decided not to use LIMIT syntax for SQL calls in MS SQL Server. Instead they use TOP syntax. Ie:

  MySQL would use:

  SELECT x,y,z

  FROM table

  LIMIT 0,29

  To get the first 30, and would use:

  SELECT x,y,z

  FROM table

  LIMIT 30,59

  To get the next 30 and so on...

  On the other hand, the only syntax available in MS SQL Server is the TOP syntax which is:

  SELECT TOP 30 x,y,z

  FROM table

  There is no way with that db to get rows 30-60. The only alternative is to switch back to CSV mode or change db's to MySQL/PostgreSQL.

  ----------

  Many thanks

  Scott

   

   

   

• SteveB-74825

  Hall of Fame

  Points: 3290

  More actions

  September 10, 2004 at 4:32 am

  #522505

  check out http://forums.devshed.com/archive/t-166713 contains a somewhat messy solution that would slow down pretty quickly the 'deeper' you wanted to delve.

   

  cheers

  dbgeezer

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  September 10, 2004 at 5:23 am

  #522512

  Is it possible for you to ADD an IDENTITY field or some variant thereof that would allow the following type syntax?

  SELECT field1, field2, field3

    FROM dbo.table1

  WHERE table1ID BETWEEN 1 AND 30

  Then you could do ...

  WHERE tableID BETWEEN 31 AND 59 etc....

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net