Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: LIMIT in MSSQL

  1. #1
    Join Date
    Oct 2003
    Posts
    15

    Unanswered: LIMIT in MSSQL

    hey, im trying to convert my mysql query statement into mssql
    but i can't find the MSSQL function for LIMIT.. so i can limit the amout of rows to select

    [Incorrect syntax near 'LIMIT'. ]

    mysql query: select a,b,c from alfa limit 0,5

    this shows only 5 rows while my table contains over like 1000 records...
    anyone who can help me with the LIMIT function for MSSQL please?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    TOP

    select TOP 5 a, b, c from alfa

    rudy
    http://r937.com/

  3. #3
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    have a look at the "top" keyword in BOL - that may be what you are looking for.
    Regards
    Dbabren

  4. #4
    Join Date
    Nov 2003
    Location
    Colombo ,Sri Lanka
    Posts
    7
    From BOL

    Limiting Result Sets Using TOP and PERCENT
    The TOP clause limits the number of rows returned in the result set.

    TOP n [PERCENT]

    n specifies how many rows are returned. If PERCENT is not specified, n is the number of rows to return. If PERCENT is specified, n is the percentage of the result set rows to return:

    TOP 120 /*Return the top 120 rows of the result set. */
    TOP 15 PERCENT /* Return the top 15% of the result set. */.

    If a SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the ordered result set. The entire result set is built in the specified order and the top n rows in the ordered result set are returned.

    The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP in these ways:

  5. #5
    Join Date
    Oct 2003
    Posts
    15
    thanx guys, this helps
    but now i wonder how to specify:

    top 120 - limits to 120 results
    but now i want to limit in a range, example:

    mysql query: select a,b,c from alfa limit 50,150

    so this means, select only the rows between 50 and 150...
    it seems that just TOP won't do the job... anyone knows?

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    How is the data being ordered ?

  7. #7
    Join Date
    Oct 2003
    Posts
    15
    wel its not being ordered or grouped by...
    just want to set a limit... or is it required to order firsT?

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    It is not required - but normally you want the top X based on some criteria - otherwise the relevance of the data is unknown.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    to do the equivalent of LIMIT 50,150, you definitely have to sort by something

    select *
    from (
    select top 150 a, b, c
    from (
    select top 200 a, b, c
    from alfa
    order by a asc )
    order by a desc )
    order by a asc

    or something similar


    rudy

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    Basically, follow the format:

    select top Y-X * from (select top Y * from table order by field1) aliasname order by field1 desc

    Y = 150
    X=50

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rnealejr, LIMIT 50,150 in mysql returns 150 rows starting at the 51st row (the first row is offset 0)

    so your example is slightly off because Y-X is only 100

    also, you need to do another sort back to the original sequence, your example shows them in reverse

    sorry to be so picky

    i forgot the derived table aliases in my example, but i believe it has the right approach -- select 200, reverse, select 150, and reverse back


    rudy

  12. #12
    Join Date
    Feb 2002
    Posts
    2,232
    That is why I left them as variables and filled them in with what he described as
    so this means, select only the rows between 50 and 150...
    . Changing the order of the final result is his choice.

    From either of our examples, he should be able to figure it out.

    Happy Thanksgiving everyone.

  13. #13
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    which is better, set rowcount 100 or select top 100?

    I don't think you always get the same results, but just for interests sake...

  14. #14
    Join Date
    Feb 2002
    Posts
    2,232
    select top is the better choice.

  15. #15
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    why?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •