Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: Use of DISTINCT keyword

    If I use DISTINCT isn't there a rule where it must be the first field selected? Also, there can only be one DISTINCT field in a query, correct?

    ie,

    SELECT DISTINCT fieldA, fieldB
    FROM tableA


    but not

    SELECT fieldA, DISTINCT fieldB
    FROM tableA

    or

    SELECT DISTINCT fieldA, DISTINCT fieldB
    FROM tableA


    thanks again, this is a great forum

    ddave

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    1 distinct per select...all columns in the select will only see a set of distinct values, as a group

    Code:
    USE Northwind
    GO
    
    SELECT DISTINCT CustomerId, EmployeeId 
    	   FROM Orders
    Unlike the following which gives you more info...but is more expensive to run, because there is more work involved.

    Code:
    USE Northwind
    GO
    
    SELECT CustomerId, EmployeeId, Count(*) AS Helped 
    	   FROM Orders
    GROUP BY CustomerId, EmployeeId
    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.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    DISTINCT is not a field. It is a clause that applies to the entire resultset.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Posts
    193
    Originally posted by blindman
    DISTINCT is not a field. It is a clause that applies to the entire resultset.
    Thank you for pointing that out. I knew that but my choice of words was poor. What I meant was must it be applied to the first field selected? Thanks again.

    ddave

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah, the first and every column in the select....
    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
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, but understand that it is not applied to any "field", first, second, or last, but instead applies to the entire result set.

    The exception would be count(distinct FIELD) which applies to just the specified field.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Sep 2003
    Posts
    65
    DISTINCT needs to come before the columns names your selecting, however it refers to every column in the result set.

    Using your example of:

    SELECT DISTINCT fieldA, fieldB
    FROM tableA

    let's say tableA looks like this:

    TableA
    ID fieldA, fieldB, fieldC
    1 5 5 4
    2 5 4 1
    3 2 1 3
    4 2 1 3
    5 5 5 9

    Now the select statement will select all DISTINCT fieldA, fieldB 'combinations'

    ie, the result set will be

    5, 5
    5, 4
    2, 1

    Hope this helps,
    -Ashleigh
    -Ashleigh

Posting Permissions

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