Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2010
    Posts
    1

    Unanswered: Several tables with max fields

    Hi.

    I trying to get some help building an sql statement for the follwing cenario :

    I have the following tables :

    W1A0 ( custumer general data )
    ====
    CLIENT_REFERENCE
    LAST_NAME_COY_NAME
    FIRST_NAME
    MIDDLE_NAMES
    TIMESTAMP_UPDATED

    W1V0 ( customer extra data )
    ====
    CLIENT_REFERENCE
    MARITAL_STATUS
    FISCAL_NUMBER
    BIRTH_DATE
    SEX
    OCCUPATION_CODE

    W1L0 ( customer address data )
    ====
    CLIENT_REFERENCE
    ADDRESS_CATEGORY
    ADDRESS_LINE_1
    ADDRESS_LINE_2
    TOWN_SUBURB
    POST_CODE
    TIMESTAMP_UPDATED

    I would like to get all my customers W1A0 data ( only one row per customer that must be the max timestampo_updated ), all my customers W1V0 data and the W1L0 data that as the max TIMESTAMP-UPDATED from customer that matches the W1A0 customer.

    can you help me ? thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try this:

    Code:
    with t1 (client_reference,max_update) as 
    (select client_reference,max(timestamp_updated) 
    from w1a0
    group by client_reference)
    select a.*,b.*,c.*
    from t1
    inner join w1a0 as a on (t1.client_reference = a.client_reference and t1.max_update = a.timestamp_updated)
    inner join w1v0 as b on (t1.client_reference = b.client_reference)
    inner join w1l0 as c on (t1.client_reference = c.client_reference and t1.max_update = c.timestamp_updated)
    Andy

Posting Permissions

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