Several tables with max fields

jorgepeneirol · #1 (**permalink**) 12-30-10, 11:53

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

ARWinner · #2 (**permalink**) 12-30-10, 12:51

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

Internet Services

Web Development

Digital Marketing

Consumer Tech