Post reply

Extacting nth and nth row + 1

  • June 27, 2007 at 3:39 pm

    #179591

    Greetings,

    I have a need to extract the nth row and the nth + 1 row from a table of last names. The table in the db has an ID but I don't think I can count on it b/c I need to handle the case in which the rows are deleted or whatever. So I thought I'd load these names into a temp table variable and have them re assign a temp ID. Then from this table I can extract the nth row. So far this works great (See below). However, I need the nth + 1 row as well. Ideally I'd like a result set containing the following.

    LastName, NextLastName

    Can anyone help me out? Here is what I have so far:

    DECLARE @NthRow INT

    SELECT @NthRow = 5

    DECLARE @raw TABLE

    (

    ID int IDENTITY(1,1) PRIMARY KEY,

    LastName varchar(100)

    )

    INSERT INTO @raw (LastName)

    SELECT LastName

    FROM KeyHolders

    WHERE KeyHolderID > 0

    ORDER BY LastName

    --Get the nth row

    SELECT LastName

    FROM @raw

    WHERE ID % @NthRow = 0

    Ok, now how can I get the nth row + 1 LastName and place it in my results?

    Thanks in advance.

    Regards,
    Bob Szymanski
    Blue Mule Software

  • June 27, 2007 at 3:47 pm

    #715651

    SELECT R1.LastName, R2.LastName

    FROM @raw R1

    INNER JOIN @raw R2 ON R1.ID + 1 = R2.ID

    WHERE R1.ID % @NthRow = 0

    _____________
    Code for TallyGenerator

  • June 27, 2007 at 4:02 pm

    #715653

    declare @lastname varchar(100),@nextlastname varchar(100)

    set @lastname = (select lastname from @raw where id = @nthrow)

    set @nextlastname = (select lastname from @raw where id = @nthrow+1)

    select @lastname,@nextlastname

  • June 27, 2007 at 5:06 pm

    #715665

    Great, works as advertised - well done. Now I need a slight tweak, sorry.

    What I now need in the result set is slightly different

    Very first last name, Nth last name

    Nth + 1, 2 * Nth last name

    .

    .

    .

    For example. Given an array of 10 names and @NthRow = 3

    Alpha

    Baker

    Charlie <-- N

    Delta <-- N + 1

    Echo

    Foxtrot <-- 2N

    Gulf <-- 2N + 1

    Hotel

    Indigo

    Juliet <-- 3N

    I need the following results:

    Alpha,Charlie

    Delta,Foxtrot

    Gulf,Indigo

    Juliet,Unknown

    Thanks again.

    Regards,
    Bob Szymanski
    Blue Mule Software

  • June 27, 2007 at 5:20 pm

    #715667

    INNER JOIN @raw R2 ON R1.ID + N - 1 = R2.ID

    WHERE R1.ID % (@NthRow-1) = 0

    _____________
    Code for TallyGenerator

  • June 27, 2007 at 6:20 pm

    #715673

    Alllright!! Works great.

    Thanks, did you have to make it look so easy?

    Regards,
    Bob Szymanski
    Blue Mule Software

  • June 27, 2007 at 6:32 pm

    #715675

    I gotta ask... what will this be used for?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • June 27, 2007 at 9:10 pm

    #715697

    We have an ASP.NET Web Application that configures an Access Control System.

    Key Holders (Last Names) are presented in a dynamic tree where nodes are generated on the fly.

    If a request is made to see all the Key Holders, (could be very, huge! may in the 10s of thousands!) we can't return all of them at once cuz' it takes too long. Therefore we have to break the nodes down into subgroups. These subgroups will be defined using the Nth and N + 1 names you helped me with.

    Clear as mud, right?

    Thanks again for your help!

    Regards,
    Bob Szymanski
    Blue Mule Software

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply