• aaron i'm sure you know that SQL doesn't order data in any specific order without an explicit ORDER BY clause.

    in your case, i'm thinking you might do what you want with a CASE statement, but i'm a little unclear on how the records actually match;

    here's my first guess:

    SELECT t1.*

    FROM #test t1

    LEFT OUTER JOIN #test t2

    ON t1.ref = t2.ref

    and t1.oldvalue = t2.newvalue

    ORDER BY

    t1.ref,

    CASE WHEN t1.oldvalue is NULL THEN 1

    WHEN t1.oldvalue = t2.newvalue then 2

    else 3

    end,

    seqno

    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!