Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Sep 2002
    Location
    Adelaide, Australia
    Posts
    472
    Thanks
    3
    Thanked 0 Times in 0 Posts

    next record filter

    Hi.

    How can I show duplicates of two fields, like the same of the firstname and lastname?
    For example if I have;

    firstname lastname
    John Smith
    John Smith
    Michael Owen
    Michael Smith

    it would show me... so the user can view them and delete the one thats wrong.
    John Smith
    John Smith

    I found a way to do this, but it only would show the first of the duplicate entries. I need it to show all of them.
    Last edited by reubenb; 12-19-2007 at 04:55 AM. Reason: realised i was asking for something else

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    http://www.cryer.co.uk/brian/sql/sql_crib_sheet.htm

    Code:
    select field1, field2, count(*) from TableName
    group by field1, field2
    having count(*) > 1

  • #3
    Regular Coder
    Join Date
    Sep 2002
    Location
    Adelaide, Australia
    Posts
    472
    Thanks
    3
    Thanked 0 Times in 0 Posts
    that works, but it only gives me one record that is duplicated. there are other fields that are different.

    (in my db, if there are two records, mostly a lot of the other fields would be completed, but in another there would only be a few [incomplete])

    in my original example, your code outputs
    'John Smith'
    where I want it to output
    'John' | 'Smith'
    'John' | 'Smith'

    any idea?


  •  

    Posting Permissions

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