Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: return 7 col but DISTINCT on 3 col from 2 tables

    Hello !
    for MS SQL 2000
    Code:
    SELECT tableA.idA, tableB.idB,tableB.ColX,tableA.ColA,
    tableA.ColB, tableA.ColC
    FROM tableB RIGHT OUTER JOIN tableA ON tableB.idB = tableA.idA
    I want to do a DISTINCT on

    tableB.ColX, tableA.ColA, tableA.ColB
    to return only the rows tableB.ColX, tableA.ColA, tableA.ColB which are differents

    thank you for helping

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    what values do you want for the other 4 columns

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    the normal values

    lets say I need A, B, C, D , E, F
    but DISTINCT on A, B, C (you can forget the 2 tables)

    if A,B,C are identics I keep anyone of them, Max(A) for exemple

    thank you

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi anselme

    Could you post some example results
    a) Of the data as it would appear BEFORE the distinct is applied OR the raw data as it would appear in the tables
    b) the results you are hoping for

    As an example (without distinct) a)
    Code:
    A    B     C     D     E      F
    ------------------------------
    aa   bb   cc    1     2      3
    aa   bb   cc    4     5      6
    with distinct I think you would want b)
    Code:
    A    B     C     D     E      F
    -------------------------------
    aa   bb   cc    ??    ??     ??
    Is this accurate for your problem?
    What would go where the ?? are?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select A, B, C, min(D), max(E), avg(F)
      from daTable
    group by A, B, C
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    a smart quick SQL in the last post. however i dont think that is what you want. perhaps you need to relook the db design itself. with a proper design this kind of a requirement should not at all come up.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah Rudy - come on!
    You could have written a CLR aggregate function or something to better meet the business requirements stated. SQL Consultant my Aunt Fanny.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by upalsen
    a smart quick SQL in the last post. however i dont think that is what you want. perhaps you need to relook the db design itself. with a proper design this kind of a requirement should not at all come up.
    oh, boy, this is getting interesting

    upalsen, if you wouldn't mind, could you please show how A,B,C,D,E,F can be redesigned so that the requirement for the query in post #4 "should not at all come up"

    i'd really like to see how you do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    sorry Rudy, you are getting me wrong. I never said that the database can be designed to address the query just how Anselme wanted. what I wanted to say is this query should not be a requirement at all as Anselme never asked for a sum or avg. rather just wanted a distinct row set from a one to many relationship that too with an outer join which appears to be nonrealastic and absurd. or the data is such that they can be there in the first table itself, if normalized properly .

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    would you kindly repeat what you just said? i'm having a little trouble understanding it

    also, i really want to know why you think his outer join is unrealistic and absurd
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    ok. I am ready to withdraw all my comments if you can show me the distinct output without using aggregate functions. mind that the original post never ever asked for any sum, avg, or max. it just wanted a distinct set of rows of 3 cols while selecting 7 cols from a one-to-many relationship

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there is a flaw in the original specification, which you seem to have propagated -- "a distinct set of rows of 3 cols while selecting 7 cols"

    first of all, you were pretty close when you said "a distinct set of rows" because it is rows that are distinct

    so if you put 7 columns into the row, you cannot do a "distinct" on just 3 of them

    but i suspect you knew that already, right?

    the alternative is to either

    1. use DISTINCT and only have 3 columns in the SELECT

    or

    2. use GROUP BY on the 3 columns, but then you are forced to use an aggregate function on the other 4

    my question to you -- given columns A,B,C,D,E,F, how would you produce a result set where A,B,C, are unique? what would you do for the other 4 columns?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by upalsen
    ok. I am ready to withdraw all my comments if you can show me the distinct output without using aggregate functions.
    OK. Just to stir the pot:
    Code:
    select	A,
    	B,
    	C,
    	(select top 1 D from daTable subtable where subtable.A = daTable.A and subtable.B = daTable.B and subtable.C = daTable.c) as D,
    	(select top 1 E from daTable subtable where subtable.A = daTable.A and subtable.B = daTable.B and subtable.C = daTable.c) as E,
    	(select top 1 F from daTable subtable where subtable.A = daTable.A and subtable.B = daTable.B and subtable.C = daTable.c) as F
    from	daTable
    group by A,
    	B,
    	C
    There. Now you can both be pi$$ed at me instead of eachother.
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Dec 2005
    Posts
    266
    thank you r937, for my needs your answers works fine

    min,min,min or max,max,max no matter, sometimes you need tree different girls and you dont care of the color of the shoes

    thank you to everybody, and blindman for the other solution

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by anselme
    sometimes you need tree different girls and you dont care of the color of the shoes
    ...and thank you for that colorful metaphore.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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