Results 1 to 5 of 5

Thread: Mssql Limit?

  1. #1
    Join Date
    Jul 2002
    Posts
    87

    Angry Unanswered: Mssql Limit?

    Hello out there!
    I've just read in a MySQL Page that there is an old command named 'LIMIT' for MySQL e.g.:
    SELECT * FROM Table WHERE A=2 ORDER BY B LIMIT 500,20;
    -> will output the 20 first rows after rows # 200

    is there anything like that for MsSQL?
    I could really use that for big searches with page numbers, you wouldn't have to get all recordsets and move to record afterwards...

    Anybody any idea?

    Thanks!
    greetz

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SET ROWCOUNT sets a maximum number of rows to return for the duration of your connection.
    TOP n [PERCENT] is specific to your SQL statement.

    blindman

  3. #3
    Join Date
    Jul 2002
    Posts
    87
    Originally posted by blindman
    SET ROWCOUNT sets a maximum number of rows to return for the duration of your connection.
    TOP n [PERCENT] is specific to your SQL statement.

    blindman
    Hi Blindman,
    But with ROWCOUNT or TOP i cannot take a 'snapshot' from the mid of the recordsets, right?

    e.g. from record # 20 to 50...
    Currently im working with SELECT TOP 20 ...
    but these are just the first 20 recordsets - right?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, you can get creative like this

    select top 20 *
    from
    (select top 50 *
    from table
    order by KeyField asc) Subquery
    order by Keyfield desc

    This would give you records 31-50 (I think. It's late in the day for me now...).

    The drawback of the TOP statement is that you have to hardcode the value. You can pass it a parameter like TOP @Number_I_Want without creating the statement as a string and then executing it dynamically.

    You can also skin this cat with cursors, but that would be painful for both you and the cat.

    The best way to do this will depend on your specific requirments.

    blindman

  5. #5
    Join Date
    Jul 2002
    Posts
    87
    Originally posted by blindman
    Well, you can get creative like this

    select top 20 *
    from
    (select top 50 *
    from table
    order by KeyField asc) Subquery
    order by Keyfield desc

    This would give you records 31-50 (I think. It's late in the day for me now...).

    The drawback of the TOP statement is that you have to hardcode the value. You can pass it a parameter like TOP @Number_I_Want without creating the statement as a string and then executing it dynamically.

    You can also skin this cat with cursors, but that would be painful for both you and the cat.

    The best way to do this will depend on your specific requirments.

    blindman
    hey blindman,
    that's a possibility i didnt think of... .. thanks!
    i'll try it with subqueries...

    greetz

Posting Permissions

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