|
|
Posted by Razvan Socol on 10/06/06 14:09
Considering the following DDL and sample data:
CREATE TABLE TheTable (
id int PRIMARY KEY,
email varchar(50) NOT NULL,
name varchar(50) NULL,
address varchar(50) NULL,
joinedon datetime NOT NULL,
UNIQUE (email, joinedon)
)
INSERT INTO TheTable VALUES (1,
'j@smith.com','johnathan',NULL,'19950101')
INSERT INTO TheTable VALUES (2, 'j@smith.com',NULL,'barcelona
street','19980101')
INSERT INTO TheTable VALUES (3, 'j@smith.com','john','valencia
street','19970101')
INSERT INTO TheTable VALUES (4, 'j@smith.com','john Q',NULL,'19990101')
The following query provides the expected result:
SELECT x.email, (
SELECT a.name FROM TheTable a
WHERE a.email=x.email
AND a.joinedon=(
SELECT MAX(b.joinedon) FROM TheTable b
WHERE b.email=a.email AND b.name IS NOT NULL
)
) as name, (
SELECT c.address FROM TheTable c
WHERE c.email=x.email
AND c.joinedon=(
SELECT MAX(d.joinedon) FROM TheTable d
WHERE d.email=c.email AND d.address IS NOT NULL
)
) as address,
x.joinedon
FROM (
SELECT email, MAX(joinedon) as joinedon
FROM TheTable
GROUP BY email
) x
Razvan
mrclash wrote:
> Hello,
>
> I have a Database in a SQL Server 2000 where I have different users
> tables with equal fields like this:
>
> id (int)
> email (varchar)
> name (varchar)
> address (varchar)
> joinedon (datetime)
>
> I want to merge all the tables in one taking rows with the same email
> but taking always the more recent fields based on the 'joinedon' field.
> So if I have this four different rows:
>
> Tbl email name address joinedon
> --------------------------------------------------------------------------------------------
> T1 j@smith.com johnathan NULL 01/01/95
> T2 j@smith.com NULL barcelona street 01/01/98
> T3 j@smith.com john valencia street 01/01/97
> T4 j@smith.com john Q NULL 01/01/99
>
> And the final row entered in the new table would be
>
> Tbl email name address joinedon
> ----------------------------------------------------------------------------------------
> new j@smith.com john Q barcelona street 01/01/99
>
> I am trying doing his with union statements, but i am not getting the
> real merging of data. Any clue?
>
> Thanks for your help.
Navigation:
[Reply to this message]
|