Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    9

    Unanswered: Limit the number of records (with offset)

    Hi,

    i want to have only a few rows in a table. eg, from row 20 to 50. The problem is, 20 is a variable and 30 is (50 - 20 = 30...) one too
    i use this statement:

    SELECT * FROM
    (SELECT TOP @MaxRecord * FROM
    (SELECT TOP @MaxRecord * FROM
    (SELECT TOP @Totals * FROM PL4 ORDER BY RailLocation ASC)
    AS foo ORDER BY RailLocation DESC )
    AS bar )
    AS baz ORDER BY RailLocation ASC

    Totals is 50 and
    MaxRecord is 30 in this example
    PL4 was declared before in this Stored Procedure

    This aint allowed, only when i hardcoded top 30 and top 50 it works...
    And i don't want it to be hardcoded, does someone have a solution for this problem?

    Thnx

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Limit the number of records (with offset)

    Try this:

    EXEC('SELECT * FROM
    (SELECT TOP ' + @MaxRecord + ' * FROM
    (SELECT TOP ' + @Totals + ' * FROM PL4 ORDER BY RailLocation ASC)
    AS foo ORDER BY RailLocation DESC )
    AS baz ORDER BY RailLocation ASC')
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    9
    Nice, it works, thanx

Posting Permissions

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