Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2004
    Posts
    5

    Unanswered: Multiple DISTINCTS?

    Hello,
    Is it possible to constrain a query so that I receive only the duplicate rows (all column data matches)? For example:
    SELECT DISTINCT COL1,
    DISTINCT COL2,DISTINCT COL3
    FROM TABLE

    I did a search on multiple DISTINCTS but came up with nothing. Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    DISTINCT applys to the whole row of the result set, not individual columns of it. When you do a
    PHP Code:
    SELECT DISTINCT col1col2col3
       FROM myTable 
    you will get one row for any given combination of col1, col2, and col3. You can use DISTINCT within aggragate functions to get aggrigates of the unique values, but when applied against a result set, DISTINCT applies to the whole set (row).

    -PatP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    uh, huh?

    SELECT DISTINCT Col1, Col2, Col3

    Will give you 1 unique row..

    You want to only see dups?

    SELECT Col1, Col2, Col3
    FROM myTable99
    GROUP BY Col1, Col2, Col3
    HAVING COUNT(*) > 1

    Is that what your after?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Apr 2004
    Posts
    5
    Originally posted by Brett Kaiser
    uh, huh?

    SELECT DISTINCT Col1, Col2, Col3

    Will give you 1 unique row..

    You want to only see dups?

    SELECT Col1, Col2, Col3
    FROM myTable99
    GROUP BY Col1, Col2, Col3
    HAVING COUNT(*) > 1

    Is that what your after?
    ----------------------------------------------------------

    Thanks for your suggestions. I am looking for a resultset of:

    COL1 COL2 COL3
    ----------------------------
    brown tall dog
    brown tall dog
    brown tall dog


    Thanks again for your help.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did the code give you what you wanted...I'm not sure...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Apr 2004
    Posts
    5
    Originally posted by Brett Kaiser
    Did the code give you what you wanted...I'm not sure...

    Not yet, but I could be doing something wrong.

    I've tried the following:
    SELECT DISTINCT COL1, COL2, COL3
    FROM myTABLE

    and:
    SELECT COL1, COL2, COL3
    FROM myTABLE
    GROUP BY COL1, COL2, COL3

    and:
    SELECT DISTINCT COL1, COL2, COL3
    FROM myTABLE
    HAVING COUNT(*) > 1

    and I've tried:
    SELECT COL1, COL2, COL3
    FROM myTABLE
    GROUP BY COL1, COL2, COL3
    HAVING COUNT(*) > 1

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well that should work...

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 varchar(25),Col2 varchar(25),Col3 varchar(25))
    GO
    
    INSERT INTO myTable99(Col1,Col2,Col3)
    select 'Brown','Tall','Dog' UNION ALL
    select 'Brown','Tall','Dog' UNION ALL
    select 'Brown','Tall','Dog' UNION ALL
    select 'Blonde','Small','Pussy cat' UNION ALL
    select 'Red','Medium','Snapper Turtle'
    GO
    
    SELECT Col1, Col2, Col3
    FROM myTable99
    GROUP BY Col1, Col2, Col3
    HAVING COUNT(*) > 1
    GO
    
    DROP TABLE myTable99
    GO
    Oh, are you looking for all three?
    Last edited by Brett Kaiser; 04-01-04 at 17:30.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    SELECT * FROM myTable99 o WHERE EXISTS(
    SELECT *
    FROM myTable99 i
    WHERE o.Col1 = i.Col1
      AND o.Col2 = i.Col2
      AND o.Col3 = i.Col3
    GROUP BY Col1, Col2, Col3
    HAVING COUNT(*) > 1)
    GO
    All three....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Apr 2004
    Posts
    5

    Thanks so much!

    Brett,
    That should do it! Combined with the info in the links provided in this thread: http://www.dbforums.com/t991775.html

    I should be able to put something together. I really do appreciate all your hard work.

    Regards,
    Americus Johnson

    Thanks also to Pat!

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hard Work...Lord no...

    That's why I became a dba....



    PS Don't forget to take ALL of your animal freinds...ya might get lucky
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Apr 2004
    Posts
    5
    Originally posted by Brett Kaiser
    Hard Work...Lord no...

    That's why I became a dba....



    PS Don't forget to take ALL of your animal freinds...ya might get lucky

    True dat.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    yup
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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