Finding duplicates using SQL 1

[rboscia]

I am a newbie that needs help. (I also posted this to the Oracle Reports, hoping I would get an answer somewhere)
I have a table that contains customer telephone numbers (the telephone number is in 2 separate fields)and also a customer ticket #. I want to retrieve all the duplicates (and only the duplicates) of those telephone numbers by ticket #.
My output should look like this

Telephone # Ticket #
xxx-xxx-xxxx 1
8
Total # of Occurances ##

Telephone # Ticket #
xxx-xxx-xxxx 3
7
15
Total # of Occurances ##

I've tried various select queries, but not getting only the dups. Any help would be appreciated.
Thanks
Robin

 

[karluk]

If I understand correctly you want all combinations of telephone number and ticket number that are in your table multiple times. You should be able to get this with the following query

select telephone#, ticket#, count(*) from your_table
group by telephone#, ticket#
having count(*) > 1

 

[Guest_imported]

As i can understand u mant to select the dublicate row int the table with same telephone no and Ticket #

select a.telephone no,a.ticket # from table a , table b
where a.telephone no = b.telephone No
and a.ticket # = b.ticket #
and a.rowid <> b.rowid;


From sanjay mamgain
Mamgain_sanjay@yahoo.com

 

[rboscia]

Yes, the post from sanjay mamgain worked! Thanks so much
Robin

 

[rboscia]

the post from sanjay works, however now i have one more question...
the users also want to be able to enter a date range to get their duplicates that occurred between that date range.
i add this to the where clause

and a.date >= _firstdate
and a.date <= _enddate

and this is what the output looks like

Telephone Number Ticket #
123-456-7890 12
22

Total # of Occurances 2

Telephone Number Ticket #
234-456-7890 45

Total # of Occurances 1

telephone Number 234-456-7890 has another ticket in the database that is a duplicate, however that duplicate is not within that specified date range. I would like the output to show the duplicates that are within that date range only.

thanks again &amp; hope this is not too confusing

 

[RandyD]

add this also:

and b.date >= _firstdate
and b.date <= _enddate

hth
Randy

 

[rboscia]

Randy,
That was SO easy!
Thanks so much for your help