SQL find dupicate select statment

Forum|Forum|19 years ago
May 9, 2007
7 replies
569 views

Questoin on syntax.

This works and does a join to pull all the records:

"SELECT a.*,b.* FROM dataBase.members AS a LEFT OUTER JOIN
dataBase.group_name AS b ON a.user_newsletter = b.group_id
ORDER BY user_joind_date DESC"

No I need to check for duplicate IP addresses and pair them together with the newest date first. So for example if we find say 100 records that have duplicates it would pair the duplicates togeter. And start with the newest date first even if the second or third and so on duplicates have older dates.

Like this:

Record: 85 IP:200.118.166.193 Date: 5/9/2007
Record: 65 IP:200.118.166.193 Date: 2/9/2007
Record: 35 IP:200.118.166.193 Date: 1/10/2007
Record: 84 IP:72.92.3.62 Date: 5/8/2007
Record: 85 IP:72.92.3.62 Date: 5/7/2007

And I know the following is not correct.

Any one the correct way to write what I am trying do?

"SELECT a.*,b.*
count(a.user_IP) AS NumOccourrences
FROM dataBase.members AS a LEFT OUTER JOIN
dataBase.group_name AS b ON a.user_newsletter = b.group_id
Group by a.user_IP Having ( COUNT(a.user_IP) > 1 )
ORDER BY user_joind_date DESC"

Server side applications

This topic has been closed for replies.

N

Newsgroup_User

Inspiring

JimBo wrote:

> Which from what I can see would produce an error!
>
> Anyway, why use a temp table when you do not need to? That is an
> enormous waste of resources which is simply not needed.
>

I got the idea this was a one time thing, not recurring. In my
reasoning a temp table would consume less time start to finish.

N

Newsgroup_User

Inspiring

Which from what I can see would produce an error!

Anyway, why use a temp table when you do not need to? That is an enormous
waste of resources which is simply not needed.

"Philo" <meansyou@nospam.net> wrote in message
news:f1v6bd$8i3$1@forums.macromedia.com...
>I would do it something like
>
> select id, count(fld) cnt into #temp
>
> Select s.flds
> where temp.id = s.id and temp.cnt > 1
>
>
>
> --
>

N

Newsgroup_User

Inspiring

I would do it something like

select id, count(fld) cnt into #temp

Select s.flds
where temp.id = s.id and temp.cnt > 1

--

N

Newsgroup_User

Inspiring

Why are you using SELECT *?

If you want to see the entire record from all records with duplicate IPs,
you'll need a subquery.
It will look something like this:

SELECT A.Record, A.User_IP, A.User_Join_Date, [anything else you want to see
for this task]
FROM database.members A
INNER JOIN (SELECT User_IP FROM database.members GROUP BY UserIP HAVING
COUNT(*) > 1) AS A_Dups ON A.User_IP = A_Dups.User_IP
ORDER BY A.User_IP, A.User_Join_Date

I didn't see anything from your groups table in the results you wanted, so I
left it out.
Above, you're joining your members to a derived table that contains a list
of all IPs that are duplicated. That means the resulting join gives you all
records that have an IP duplicated in some other record. The ORDER BY then
puts them in the order you wanted - all the IPs together with the oldest
record first.

Hope that helps.