I have a list of names and ID's, with many multiplication (it's a payroll list). I'd like to see those ID's and names where the name changed somewhen.
Input:
id1 name
1 Josephine Baker
2 Norma Jeane Morteson
2 Marilyn Monroe
33 Sandra Bullock
15 Issur Danielovitch
1 Freda Josephine McDonald
15 Kirk Douglas
28 Meg Ryan
28 Margaret Hyra
36 Vin Diesel
36 Mark Sinclair
8 Sean Connery
11 Mel Gibson
16 Julia Roberts
Desired output:
ID Name
1 Freda Josephine McDonald
1 Josephine Baker
2 Marilyn Monroe
2 Norma Jeane Morteson
15 Issur Danielovitch
15 Kirk Douglas
28 Margaret Hyra
28 Meg Ryan
36 Mark Sinclair
36 Vin Diesel
I'm running this query. It works well, it is fast enough, I just have a feeling that this could be a lot more professional.
SELECT first(ST.id1) as ID , first(ST.nam) as Name
FROM Stars AS ST
INNER JOIN
(select id1, first(nam), count(id1) from
(select [ST1.id1]&[ST1.nam], first(ST1.id1) as id1, first(ST1.nam) as nam,
count(*) from Stars ST1
group by [ST1.id1]&[ST1.nam])
group by id1
having count(id1)>1) AS SJ
ON SJ.id1=ST.id1
GROUP BY ST.id1, ST.nam
ORDER BY ST.id1;
The logic behind is to select unique id&name pairs, then select those id's that come up more than once, then (self)join the table and select the unique names to those id's.
Any advise is welcome!