Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    11

    Unanswered: Extract Records (Rows) With In A Particular Range

    Hello Friends,
    Is there any way in sql server to get records with in a particular range. Lets say that we got 100 records satisfying a query, I want only first 20 records or records in the range 40 - 60. Kindly suggest me how to accomplish this in sqlserver and if possible provide a code snippet regarding this. Thanks in advance.

    Regards,
    Ch.Praveen.

  2. #2
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    CREATE procedure sp_pagging @page INT = 1, @rows INT = 20 AS
    SET NOCOUNT ON

    DECLARE @Ilosc int
    DECLARE @LiczbaRekordow INT
    CREATE TABLE #Temp
    (
    I int IDENTITY,
    [id_kontr] [int] NOT NULL ,
    [nr_ew_up] [nvarchar] (40) NULL ,
    [nr_zaw] [nvarchar] (30) NULL ,
    [typ_kontr] [nvarchar] (30) NULL

    )

    INSERT INTO #Temp SELECT id_kontr,nr_ew_up,nr_zaw,typ_kontr FROM kontr
    SELECT @LiczbaRekordow = @@ROWCOUNT


    DECLARE @Pierwszy int, @Ostatni int
    SELECT @Pierwszy = (@page - 1) * @rows
    SELECT @Ostatni = (@page * @rows + 1)

    SELECT * , @LiczbaRekordow AS LiczbaRekordow FROM #Temp
    WHERE I > @Pierwszy AND I < @Ostatni
    SET NOCOUNT OFF
    GO

    ------------------------

    sp_pagging @page=3

  3. #3
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    in english

    CREATE procedure sp_pagging @page INT = 1, @rows INT = 20 AS
    SET NOCOUNT ON

    DECLARE @all int
    DECLARE @allrows INT
    CREATE TABLE #Temp
    (
    I int IDENTITY,
    [id_kontr] [int] NOT NULL ,
    [nr_ew_up] [nvarchar] (40) NULL ,
    [nr_zaw] [nvarchar] (30) NULL ,
    [typ_kontr] [nvarchar] (30) NULL

    )

    INSERT INTO #Temp SELECT * FROM table1
    SELECT @allrows= @@ROWCOUNT


    DECLARE @first int, @last int
    SELECT @first = (@page - 1) * @rows
    SELECT @last = (@page * @rows + 1)

    SELECT * , @allrows AS allrows FROM #Temp
    WHERE I > @first AND I < @last
    SET NOCOUNT OFF
    GO

  4. #4
    Join Date
    Nov 2003
    Posts
    11
    Hello Rafala,
    Thanks for your wonderful procedure. But can you figure out the approach to accomplish the above result without creating a temporary table.

    regards,
    Ch.Praveen.

  5. #5
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    i have no idea

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can do this without a temporary table. The technique is called paging and if you search dbforums you will find many different techniques that can be used. None of the are elegant, but most are functional and reliable.

    My preferred method requires a specific column for sorting. Basically, the procedure accepts a StartingValue and a RecordCount value as parameters. The query then selects records from your table that are greater than your StartingValue, while a subquery returns the number of records that have sortvalues less than or equal to their own sort value, thus providing an ordinal ID. The outer query then filters only the records where the ordinal ID is <= the requested RecordCount.

    Provide some DDL for your table if you need specific coding assistance.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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