Quick MySQL question

[TheKub]

I have a list of names and phone numbers in a MySQL database. I need to know all the records that have a duplicate phone number.

I know I can do "select distinct phonenumber from table" and get a list of the non duplicate numbers. I figure I need to nest 2 queries together somthing like...

select * from table where phone not
in (select distinct phone from table);

But thats not working. I figure my syntax is messed up or something.

 

[Celeryman]

I don't know if this is what you want, but you could do an order by clause. Try:

 

[PowerYoga]

try something like this: preferably you should select a primary key so the query doesn't run forever. I'll assume name is a primary key here.

edit: this will return all the names that have a duplicate phone number and how many duplicates the numbers have, I think.

 

[Armitage]

Originally posted by: PowerYoga
try something like this: preferably you should select a primary key so the query doesn't run forever. I'll assume name is a primary key here.

edit: this will return all the names that have a duplicate phone number and how many duplicates the numbers have, I think.

Close, but I don't think you can put agregate functions in the having and order clauses. Maybe, I haven't tried it. But something like this should work:

select name, phone_number, count(*) as Duplicate from table
group by name, phone_number
having Duplicate > 1
order by Duplicate desc;

 

[PowerYoga]

I tried using the Duplicate instead of count(*) and it came up with this error:

SQL0206N "DUPLICATE" is not valid in the context where it is used. SQLSTATE=42703
Error: SQL0206N "DUPLICATE" is not valid in the context where it is used. SQLSTATE=42703
(State:S0022, Native Code: FFFFFF32)

I did a quick query with the my version of funcion table and it works. You can use aggregate functions in the having and order clauses afterall. 🙂

 

[mugs]

Originally posted by: PowerYoga
try something like this: preferably you should select a primary key so the query doesn't run forever. I'll assume name is a primary key here.

edit: this will return all the names that have a duplicate phone number and how many duplicates the numbers have, I think.

That will only return records where both the name AND phone number is duplicated.

For a list of the just the phone numbers he'd want this:

Edit: i.e. if John Smith and Jane Smith have the same phone number, they wouldn't show up with your query.

 

[Armitage]

Originally posted by: PowerYoga
I tried using the Duplicate instead of count(*) and it came up with this error:

SQL0206N "DUPLICATE" is not valid in the context where it is used. SQLSTATE=42703
Error: SQL0206N "DUPLICATE" is not valid in the context where it is used. SQLSTATE=42703
(State:S0022, Native Code: FFFFFF32)

That's because it turns out that DUPLICATE is a keyword: http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html so the query parser is getting confused! I bet if you used something else it would work

I did a quick query with the my version of funcion table and it works. You can use aggregate functions in the having and order clauses afterall. 🙂

Interesting - didn't know that.