[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