none
changing Top lower margin RRS feed

  • Question

  • I been looking for this for quite a while but have not been able to find anything.

    Is there a command like MySQL Limit command, or a way to mimic that behavior in SQL?

    For those that don’t know the command it is similar to Top but allows you to say what is the first record you want to receive and how many records from there. For instance given the following example recordset:

    A
    B
    C
    D
    E
    F

    The following query would return (note Limit low margin is zero based):

    Select * From [Table] Limit 2, 3

    C
    D
    E

    So far I have figured that I may be able to create a temporary table with an identity field that would allow me to do a where against, but I’m not sure if that would be the most effective way to mimic this behavior, and again, there may be a built in way to do it that I’m not aware off.
    Thursday, April 26, 2007 8:52 PM

Answers

  • There is no built-in way. Limit is a proprietary syntax like TOP or SET ROWCOUNT. You can do something like below using ANSI SQL ranking function (will work in SQL Server 2005, Oracle, DB2):
     
    Code Snippet
    select *
    from (
    select *, ROW_NUMBER() OVER(ORDER BY some_column) as limit
    from your_table
    ) as t
    where t.limit between @start and @end -- t.limit between 3 and 5 in your case

     

     
     
     
    Thursday, April 26, 2007 11:52 PM

All replies

  • AFAIK, there is no built in command in SQL for something like that. You'd have to write a query with a TOP 3 from the table where ID > ( get the TOP 2nd ID) order by the primary key or whatever column you want the results ordered by.
    Thursday, April 26, 2007 11:38 PM
  • There is no built-in way. Limit is a proprietary syntax like TOP or SET ROWCOUNT. You can do something like below using ANSI SQL ranking function (will work in SQL Server 2005, Oracle, DB2):
     
    Code Snippet
    select *
    from (
    select *, ROW_NUMBER() OVER(ORDER BY some_column) as limit
    from your_table
    ) as t
    where t.limit between @start and @end -- t.limit between 3 and 5 in your case

     

     
     
     
    Thursday, April 26, 2007 11:52 PM