Post reply

Selecting only the Odd Numbered Rows

  • August 23, 2007 at 8:05 pm

    #176136

    Hello All,

    I know, what kind of database person am I if I am on here asking questions. Well, I have never before met anyone that knows it all, surely not me.

    I have a table, with an incrementing 'RowID' column. What I am trying to do, is select only the data from the odd numbered rows.

    Any info on how I may be able to do this would be very helpful. Even if it is thru SSIS or DTS. I like just a plain old query myself though.

    Thanks

    Andrew SQLDBA

  • August 23, 2007 at 9:09 pm

    #729150

    You need every other row or only row where id is odd?

     

    If the latter : Select * from dbo.Table where id % 2 = 1

     

    If the former, then you need to generate a new row id starting from 1 to n, then filter on that.  Check out the row number function and you'll figure out how to do it.  You'll need a derived table to apply the where clause but that's the only "problem".

  • September 6, 2007 at 3:46 am

    #731748

    Hi,

    You can try this one also

    select fieldName from dbo.table

    where cast(rowid as int)% cast(2 as int)=1

  • September 6, 2007 at 8:05 am

    #731837

    Nisha,

    Why the conversions?  2 is already an INT and RowID is likely an INT if the column is an IDENTITY column...

    --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)

Viewing 4 posts - 1 through 3 (of 3 total)

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