Post reply

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

  • 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

  • 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.

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply