DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: dstinct val

  1. #1
    Join Date
    Aug 2000
    Posts
    194
    What is the best way to achieve the following?.


    I want to, select the value of COL1 from TAB1, if there is duplicate value for COL1, COL2, COL3 combined.


    I have the following qry, but believe there would be some better ones.

    Select COL1 from (
    Select COL1, COL2, COL3, COUNT(*)
    From TAB1
    Group by COL1, COL2, COL3
    Having COUNT(*) > 1 )

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Your query requires a single full table scan and a sort on group by. If the data distribution is such that sorting requires a lot of resources then you should try the following one (and a composite index on COL1, COL2 and COL3 will be very helpful):

    SELECT col1 FROM tab1 x WHERE EXISTS
    (SELECT NULL FROM tab1
    WHERE col1=x.col1 AND col2=x.col2 AND col3=x.col3 AND rowid != x.rowid);

    Again, this might or might not be a better one, try it yourself on your actual data.

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width