How to get ID behind duplicate data

[livezone]

Hi,

My following query returns all data having duplicate data based on LastName + Last 4 Char of Phone

Select LastName , SUBSTR(Trim(Phone),Length(Trim(Phone))-3,4) as PhoneSuffix, Count(*)
from Table1
Group by LastName, SUBSTR(Trim(Phone),Length(Trim(Phone))-3,4)
having count(*) > 1

As each of these duplicate row has key valud field called ID which is not duplicate. How I can get the list of all ID's belongs to the above criteria.
Thanks
Shafiq

 

[gmmastros]

Make your select in to a subquery.

Select *
From   Table1
       Inner Join(
          Select LastName , 
                 SUBSTR(Trim(Phone),Length(Trim(Phone))-3,4) as PhoneSuffix,
                 Count(*) As Count
          from Table1
          Group by LastName, SUBSTR(Trim(Phone),Length(Trim(Phone))-3,4)
          having count(*) > 1
          ) As A On Table1.LastName = A.LastName
                    And SubStr(Trim(Phone), Length(Trim(Phone))-3,4) = A.PhoneSuffix

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[amrita418]

Fist things first, clearly seems you are working on oracle database, You should post it in the oracle forum in future.

Having said that, the code below should list all the ids you need.

select    a.uniqueid 
from      Table1 a,
          (Select       LastName , 
                        SUBSTR(Trim(Phone),Length(Trim(Phone))-3,4) as PhoneSuffix
           from         Table1
           Group by     LastName, 
                        SUBSTR(Trim(Phone),Length(Trim(Phone))-3,4)
           having       count(*) > 1) b
where      a.lastname = b.lastname
           and SUBSTR(Trim(a.Phone),Length(Trim(a.Phone))-3,4) = b.phoneSuffix

Regards,
AA