what is the sql server equivalent to mysql limit x, y

• alison

  SSC Veteran

  Points: 288

  More actions

  February 8, 2005 at 4:49 pm

  #162487

  Hi

  I've had to do a project in SQL Server and, although I haven't encountered it yet, I'm curious.  In MySQL you can say 'limit 50, 50'  to start at the 50th record and return 50 records, i.e. returns records 50 - 100. 

  How do you do the same with SQL Server?  I know about top 100 but what if I want to only start at the 50th result?

  Thanks,

  Alison

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  February 9, 2005 at 11:07 am

  #541424

  I have seen a number of examples using Row Identifiers for sending the first 50, then the next 50, etc. records to an asp output page.  I know of no function in SQL to allow you to start at the 50th record.  If your output has some type of identity, you could put the TOP 49 into a #TempTable, then SELECT TOP 50 WHERE Identity NOT IN( SELECT Identity FROM #TempTable). 

  You can also use SELECT TOP 50 WHERE NOT EXISTS( SELECT * FROM #TempTable) if you have no identity field.  Inefficient, but it should work. 

  These are bulldozer approaches... 

  I wasn't born stupid - I had to study.

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  February 9, 2005 at 11:20 am

  #541430

  Here's an exemple that is not too bulldozer but it still ain't too pretty :

  USE pubs

  DECLARE @var1 VARCHAR(12)

  DECLARE @var2 VARCHAR(30)

  SET ROWCOUNT 17

  SELECT @var1 = au_id, @var2 = au_lname FROM authors ORDER BY au_lname, au_id

  SET ROWCOUNT 0

  SELECT TOP 3 * FROM authors

  WHERE (au_id >= @var1 AND au_lname=@var2) OR ( au_lname>@var2)

  ORDER BY au_lname, au_id