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 7 of 7
  1. #1
    New Coder
    Join Date
    Jan 2013
    Posts
    17
    Thanks
    6
    Thanked 0 Times in 0 Posts

    More Efficient Query Needed

    Hi All

    Does anyone have an idea how to run this query...

    Code:
    SELECT * 
    FROM [TableName] 
    WHERE [ColumnName] IN
    (
          SELECT [ColumnName] 
          FROM [TableName] 
          GROUP BY [ColumnName] 
          HAVING COUNT(*) > 1 
    )
    ORDER BY [ColumnName]
    without the sub query?

    The sub query option seems to be taking ages to run. Is there a more efficient query that does the same thing?

    Thanks in advance for any help.

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,096
    Thanks
    23
    Thanked 594 Times in 593 Posts
    SELECT [ColumnName]
    FROM [TableName]
    GROUP BY [ColumnName] <= Not needed because that is the only thing your getting
    HAVING COUNT(*) > 1 <= This makes no sense to me because count() tells you the number of rows. Do you mean where the value of the ColumnName column is large then 1?

  • #3
    New Coder
    Join Date
    Jan 2013
    Posts
    17
    Thanks
    6
    Thanked 0 Times in 0 Posts

    More efficient query

    Sorry, it would help if I explained what I'm trying to do!

    The query is supposed to search a column (name) for duplicate values and then return all data in the table which has duplicate values in that column

    Any ideas?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,195
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Nope, I think you have it right.

    Though doing it like this *MIGHT* be more efficient:
    Code:
    SELECT T1.*
    FROM table AS T1,
         ( SELECT column, COUNT(column) AS cnt
           FROM table 
           GROUP BY column
           HAVING cnt > 1
        ) AS T2
    WHERE T1.column = T2.column
    ORDER BY T1.column
    Or even possibly (though I doubt it)
    Code:
    SELECT T1.*
    FROM table AS T1,
         ( SELECT column, COUNT(column) AS cnt
           FROM table 
           GROUP BY column
        ) AS T2
    WHERE T1.column = T2.column AND T2.cnt > 1
    ORDER BY T1.column
    May not matter, but you could try it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,195
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    It will, of course, make a *HUGE* difference if column is indexed.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    sitechooser (03-07-2013)

  • #6
    New Coder
    Join Date
    Jan 2013
    Posts
    17
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Magic!

    I ran the query on an 80MB table and it returned in 6.5 minutes. I then made the column concerned an index, ran the same query and it returned in 7.6 seconds! Bit of a difference

    Thanks a million for your help, got a critical meeting tomorrow morning and you'll make me look like superman.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,195
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    LOL! The cardinal rule of database design: When it's slow, index it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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