Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2008
    Posts
    8

    Unanswered: get the latest changed records

    Hi,

    I hava a table with the following information

    CREATE TABLE TEMP1 (REFID INT, REVISION INT, FIELDNAM VARCHAR(10), VALUE VARCHAR(10));
    INSERT INTO TEMP1 VALUES(1001, 0, 'A', 'A2');
    INSERT INTO TEMP1 VALUES(1001, 0, 'C', 'C2');
    INSERT INTO TEMP1 VALUES(1001, 0, 'E', 'E2');
    INSERT INTO TEMP1 VALUES(1002, 0, 'A', 'A3');
    INSERT INTO TEMP1 VALUES(1002, 0, 'B', 'B2');
    INSERT INTO TEMP1 VALUES(1002, 0, 'E', 'E3');
    INSERT INTO TEMP1 VALUES(1001, 1, 'A', 'A4');
    INSERT INTO TEMP1 VALUES(1001, 1, 'E', 'E4');

    Here based on latest revision and refid I should get the fieldnam and value.
    Expected output:
    REFID FIELDNAM VALUE REVISION
    1001 A A4 1
    1001 E E4 1
    1002 B B2 0
    1001 C C2 0

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If I insert one more row into your sample data, what would the result be now?
    Code:
     INSERT INTO TEMP1 VALUES(1002, 0, 'C', 'C9');

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've deleted the dupe thread - please don't duplicate your questions. Apart from anything else, it was syntactically incorrect.

  4. #4
    Join Date
    Jan 2008
    Posts
    8
    I result will be:

    REFID FIELDNAM VALUE REVISION
    1001 A A4 1
    1001 E E4 1
    1002 B B2 0
    1002 C C9 0


    ------------------------------------------------------------------
    some more information:
    I have 4 fields: A, B, C, E and have an approval process for these fields...
    at stage one (1001) the values of A, C, E changed to A2, C2, E2.
    and at stage one (1002) the values of A, B, E changed to A3, B2, E3.

    Here the latest values of A,B,C and E are A3 (from stage 1002), B2 (from stage 1002), C2 (from stage

    1001, because no changes found in 1002) and E3 (from stage 1002)....

    again another revision started from 1001
    at stage one (1001) and at revision (1) the values of A, E changed to A4 and E4

    now the latest values of A, B, C, E are A4, B2, C2, E4.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - just to ensure that I understand this in logical terms.

    For each field name, find the numerically highest refid. For that refid and fieldname combination return the row with the numerically highest revision.

    Correct?

  6. #6
    Join Date
    Jan 2008
    Posts
    8
    yes...

    For each field name, find the numerically highest refid and highest revision.

    1------>revision---1 and refid---1001 (get A, E values)
    2------>revision---0 and refid---1002 (get B value)
    3------>revision---0 and refid---1001 (get C value)

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    SQL 2k or 2005?

  8. #8
    Join Date
    Jan 2008
    Posts
    8
    I am using SQL 2000...

Posting Permissions

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