Selecting Duplicates 1

[iaresean]

Hi all,

Just have a quick question, I am sure it isn't that hard but I am struggling to find the answer.

How can I modify this query so that it shows me records which are duplicates only (i.e. have the same domain name)?

SELECT Domain_Name, Status FROM Domain WHERE Status = 'Ready';

Thanks for any and all help.

Sean.

 

[BNPMike]

If you put in a count, group by Domain_Name and add a HAVING greater than 1, you'll discard the non-duplicates.

 

[iaresean]

That sounds like it will work, thanks alot Mike.

But now for the embarasing part: I am terrible with sql. I am a beginning beginner. Could you possible show me how I would impliment this count variable into my select statement.

Here is how far I got:

SELECT count(Domain_Name), Status FROM Domain WHERE Status = 'Ready' AND count > 1 group by Domain_Name;

Is this the right track? Or I am completely off?

Thanks for your help!

Sean.

 

[BNPMike]

This should do it:

SELECT Domain_Name, Count(Someotherfield) AS CountOfEntry
FROM Domain
GROUP BY Domain_name
HAVING (((Count(Someotherfield))>1));

This counts the number of records and groups them by domain name, then discards the groups with only 1 record.

Now I'm no good at SQL so, here's a secret, I went into Access an let it write the SQL for me.

Don't blame me if it doesn't run under MySQL.