Post reply

Row in a table is out of sequence or not

  • September 4, 2009 at 9:47 am

    #209590

    Hi I would like to find out an easy way with out using temp tables to see if the result of one table column is in sequence.

    Table A

    Col 1 Col2

    1 2400

    2 2600

    3 2800

    So I would need to compare Col2 results, 2400 < 2600 that is good, and 2600 < 2800 that is good also, etc.. how do you find if they are out of order, if row 1 is 2650 for example.. Thanks,

  • September 4, 2009 at 9:56 am

    #1049484

    in this case, you join the table against a copy of itself, and compare;

    here i'm assuming col1 is an identity field:

    the key is to compare with an offset of adding one to one of the tables.

    SELECT TBA.Col1,TBA.Col2,TBB.Col1,TBB.Col2

    FROM TableA TBA

    LEFT OUTER JOIN TableA TBB

    ON TBA.Col1 = TBB.Col1 + 1

    WHERE TBA.ColB > TBB.ColB

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 4, 2009 at 10:07 am

    #1049496

    sweet, that does it, thanks!!!

  • September 5, 2009 at 12:53 am

    #1049685

    SSC Rules (9/4/2009)

    sweet, that does it, thanks!!!

    If column 1 is an IDENTITY column or has any other chance of "skipping", it won't be so sweet because the +1 idea won't work.

    Why do you need to do this without temp tables? Also, what is the PK of the example table given? I ask because there are alternate solutions if that first column proves noncontiguous and I need both pieces of info to decide which to do.

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

  • September 8, 2009 at 7:31 am

    #1050502

    You are right, I have tested it and it did not work, I ended up doing it using a temp table by creating an identity column after sorting them using the column that I wanted to check the sequence. After that, I compared my temp tables and set a flag to my code to see if they are in sequence or not, I wanted a one line or two line of code to do this, that was why I said without using temp table..

  • September 8, 2009 at 1:20 pm

    #1050742

    That's one of the easiest and fastest ways to do it. Any chance you can post the code... two reasons for that... first, it's a two way street and some folks may learn by your good example and second, there may still be an enhancement or two to be applied.

    --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 6 posts - 1 through 5 (of 5 total)

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