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:
dealer dealnum lastname firstname emplid, date
10001 7700 SMITH JOE 12345 2013-10-24
10001 4533 SMITH JOE 00000 2013-07-01
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.