Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

SQL Query DISTINCT

Options
  • 20-05-2003 3:01pm
    #1
    flyz
    Closed Accounts Posts: 536 ✭✭✭


    Can anyone tell me how to select all duplicate entries from a database table?


    that is the opposite to this :

    SELECT DISTINCT col_name FROM TableName


Comments

  • Options
    #2
    DaithiSurfer
    Closed Accounts Posts: 843 ✭✭✭DaithiSurfer


    select count(*), blah, blah2, blah etc
    from tables
    where blah
    having count(*) > 1
    group by blah, blah2, blah etc


    can't remember if the group by goes before or after the having.
    try both and see


  • Options
    #3
    flyz
    Closed Accounts Posts: 536 ✭✭✭flyz


    the following works :

    select count(*), blah
    from tables
    group by blah
    having count(*) > 1


    cheers :D


  • Options
    #4
    DeVore
    Business & Finance Moderators, Entertainment Moderators Posts: 32,387 Mod ✭✭✭✭DeVore


    Thats a nice way to do it.

    Another (and I suspect less efficient... certainly less elegant) way of doing it would be to join the table to itself, you could join on field blah and where table1.id <>table2.id

    Come to think about it that might in certain circumstances be more efficient as its using indices rather then counts.

    PM me if you need the exact SQL. My brains fried right now.

    DeV.


Advertisement