[thelist] Duplicate Records in SQL
rudy
r937 at interlog.com
Fri Apr 26 11:32:01 CDT 2002
> SELECT Name, Category, Date
> FROM tblFOO
> WHERE Name = Name
> AND Category = Category
> AND LastDate = LastDate
>
> returns all records, because 'Name = Name' is true
hi jay
what you have there isn't going to find duplicates (see below) but if you
ever need to have a condition in the where clause that's always true, use
1=1
for example, when generating the sql statement in a script, for example by
checking form fields to see if they have something in them, you will be
ANDing conditions, but you don't want to do this for the first one (this
has been discussed in a previous tip, so i won't go into a lot of
detail) --
select stuff from table
where 1=1
IF FORM.FIELD1 IS NOT EMPTY
and tablefield1 = #form.field1#
IF FORM.FIELD2 IS NOT EMPTY
and tablefield2 = #form.field2#
ETC.
anyhow, back to your question
> The ID number for each record is unique.
that makes it a bit easier
select Name, Category, Date
from tblFOO
group by Name, Category, Date
having count(*) > 1
this returns the values of all the duplicates
if you want to remove all such duplicate except one, i think in mysql
you'll end up using a temp table
rudy
More information about the thelist
mailing list