FelineConspiracy
asked on
A fairly simple SQL question
I have an Oracle table like so:
dealer, dealnum, lastname, firstname, emplid, date
I believe that in a few cases the emplid for a given person has changed. In this example Joe Smith's emplid changed from 00000 to 12345:
How can I determine the dealer, dealnum, lastname and firstname of rows where there is more than one emplid associated with a given dealer+lastname+firstname combination?
In other words I'm looking for people whose emplid has changed over time.
I know how to use row_number() with group by to get the most recent row for a given person, which would give me the current value, 12345 in this example. But that's expensive and I want first to size up how many such cases there are.
dealer, dealnum, lastname, firstname, emplid, date
I believe that in a few cases the emplid for a given person has changed. In this example Joe Smith's emplid changed from 00000 to 12345:
dealer dealnum lastname firstname emplid, date
10001 7700 SMITH JOE 12345 2013-10-24
10001 4533 SMITH JOE 00000 2013-07-01How can I determine the dealer, dealnum, lastname and firstname of rows where there is more than one emplid associated with a given dealer+lastname+firstname combination?
In other words I'm looking for people whose emplid has changed over time.
I know how to use row_number() with group by to get the most recent row for a given person, which would give me the current value, 12345 in this example. But that's expensive and I want first to size up how many such cases there are.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
awking00
What do you want to do with the information? Delete or update the earlier records or something else?
ASKER
No deletes or updates. To oversimplify a bit: Someone will be doing some manual updates, once we're confident about the data to act on. This query is part of some precautions I'm taking to avoid updating rows where, for example, someone has had multiple valid emplids at different times, as we cannot necessarily know which one to keep. So I'll just screen those people out.
ASKER
Thank you.