Results 1 to 4 of 4

Thread: SQL query: Return duplicates

  1. Oct 31st, 2003, 06:58 AM #1
    SmagO
    SmagO is offline

    Thread Starter
    Lively Member
    Join Date
    Aug 2000
    Location
    South Africa
    Posts
    113

    Question SQL query: Return duplicates

    Hi,
    could you please assist me with a SQL query. I need to analyse some data. I have two fields..call them fld1 and fld2.

    The database should never have the same combination of fld1 and fld2.

    so if record 1 has fld1=1 and fld2 = 4 then I shouldnt have this combination in any other record...
    but these duplicates unfortunately exist and I have to find them all. I have over 30 000 records in the table.

    Can you assist me with a query to find all records where the combination of fld1 and fld2 appears in more than 1 record?

    Thanks in advance
    You are living a pacifist dream, and if you dreaming it means you sleeping and you should damn well wake up!
  2. Oct 31st, 2003, 08:25 AM #2
    Teerbaby
    Teerbaby is offline
    Junior Member
    Join Date
    Apr 2001
    Location
    Baden-Baden, Germany
    Posts
    25
    Something like:

    SELECT fld1, fld2, count(fld1) as cnt from table group by fld1,fld2

    I haven't tested it, but give it a try. Any duplicates should have CNT >= 2
  3. Oct 31st, 2003, 08:30 AM #3
    DeadEyes
    DeadEyes is offline
    Frenzied Member DeadEyes's Avatar
    Join Date
    Jul 2002
    Posts
    1,196
    What about SELECT DISTINCT or am I reading the question wrong?
  4. Oct 31st, 2003, 08:33 AM #4
    DrHippyHomer
    DrHippyHomer is offline
    Lively Member
    Join Date
    Sep 2002
    Location
    Basingstoke
    Posts
    86
    SELECT fld1, fld2, COUNT(*) AS CountOf FROM tblPatient GROUP BY fld1, fld2 HAVING COUNT(*) > 1
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules


Click Here to Expand Forum to Full Width