Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2003
    Posts
    102

    Unanswered: finding DISTINCT combinations

    Hi,

    I need to get 8 columns from a table.
    1)
    I must get only one entry of each unique combination of columsn 1,2,3.
    2)
    These entries must have the max. possible value in column 4.


    Currently I have,


    SELECT DISTINCT
    one AS one,
    two AS two,
    three AS three,
    four AS four,
    five AS five,
    six AS six,
    seven AS seven,
    eight AS eight
    INTO #temp
    FROM TABLE1 A


    INSERT INTO ANSWERTABLE
    (one, two, three, four, five, six, seven, eight)

    SELECT one, two, three, four, five, six, seven, eight
    FROM #temp A
    WHERE A.four = (SELECT MAX(B.four)
    FROM TABLE B
    WHERE B.one = A.two
    AND B.two = A.two
    AND B.three = A.three);

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my policy is, i don't answer what is obviously a homework problem unless you have made an attempt yourself, and in this case, it looks like you have

    either i don't understand the problem, or else it's a lot simpler than you think
    Code:
    select one, two, three, max(four)
    from yourtable
    group by one, two, three
    GROUP BY always yields distinct combinations

    a good homework assignment makes you stop and think, doesn't it

    "distinct" does not imply you have to use the DISTINCT keyword


    rudy
    http://r937.com/

  3. #3
    Join Date
    Sep 2003
    Posts
    102

    believe it or not George isn't at home..

    believe it or not, but this isn't a homework question. I just simplified the table and column names so that it would be easier to understand.

    Thanks for your solution though.

    But i need to be able to get entries from almost all 8 columns (ie. other columns which do not have to be distinct).

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: believe it or not George isn't at home..

    Specify, what your 4 other columns must contain. You may choose from some accumulation functions like Max(), min(), avg(), sum()
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  5. #5
    Join Date
    Sep 2003
    Posts
    102

    Re: believe it or not George isn't at home..

    Originally posted by DoktorBlue
    Specify, what your 4 other columns must contain. You may choose from some accumulation functions like Max(), min(), avg(), sum()
    that's the thing, i don't want to use an aggregate function on those other columns.

    The database has numerous entries for each unique combination of columns one, two and three. And I want the entire row for the entry that has the max value at column 4.


    thanks for the help though.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    Re: believe it or not George isn't at home..

    I want the entire row for the entry that has the max value at column 4.
    you shoulda said it like that in the first place
    Code:
    select one, two, three, four, five, six, seven, eight
      from yourtable zz
     where four =
    ( select max(four)
      from yourtable
     where one=zz.one
      and two=zz.two
      and three=zz.three )
    rudy

  7. #7
    Join Date
    Sep 2003
    Posts
    102

    Re: believe it or not George isn't at home..

    Originally posted by r937
    you shoulda said it like that in the first place
    Code:
    select one, two, three, four, five, six, seven, eight
      from yourtable zz
     where four =
    ( select max(four)
      from yourtable
     where one=zz.one
      and two=zz.two
      and three=zz.three )
    rudy
    haha..
    i think i did.

    But i think I got it..check it out:


    SELECT
    one AS one,
    two AS two,
    three AS three,
    max(four) AS four
    INTO #temp
    FROM TABLE1 A
    GROUP BY one, two, three;




    SELECT
    one AS one,
    two AS two,
    three AS three,
    four AS four,
    five AS five,
    six AS six,
    seven AS seven,
    eight AS eight
    INTO ANSWERTABLE
    FROM TABLE1 A INNER JOIN #temp B
    ON (A.one = B.one
    AND A.two = B.two
    AND A.three = B.three
    AND A.four = B.four);



    I'm checking the results...and it looks good so far.

    See any bugs or loopholes ?


    Thanks again.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    bugs or loopholes? you mean, other than using a temp table where you don't really need one?

    did you try my correlated subquery?

    rudy

  9. #9
    Join Date
    Sep 2003
    Posts
    102

    Thumbs up

    Originally posted by r937
    bugs or loopholes? you mean, other than using a temp table where you don't really need one?

    did you try my correlated subquery?

    rudy
    ya thanks...
    got the same results (y)

    but for some reason i can only get it to work if the "yourtable zz" is a temp table.
    If i directly access the "yourtable" i get the following errors:

    Server: Msg 8180, Level 16, State 1, Line 38
    Statement(s) could not be prepared.
    Server: Msg 107, Level 16, State 1, Line 38
    The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
    Server: Msg 107, Level 16, State 1, Line 38
    The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
    Server: Msg 107, Level 16, State 1, Line 38
    The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you remember the correlation variable zz?

    i'd have to see your query, but i bet it's a coding error

    correlated subqueries do work in sql server

    rudy

  11. #11
    Join Date
    Sep 2003
    Posts
    102
    Originally posted by r937
    did you remember the correlation variable zz?

    i'd have to see your query, but i bet it's a coding error

    correlated subqueries do work in sql server

    rudy

    ya i named the table...
    it's funny, cause it's still a correlated query if I substitute the temp table for the outer query table.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i cannot help you if you do not show me your query


    rudy

  13. #13
    Join Date
    Sep 2003
    Posts
    102
    Originally posted by r937
    i cannot help you if you do not show me your query


    rudy
    ya sorry...
    i'm legally bound not to give out any code..
    i know it's anal, and doesn't really matter and the code's not important or anything, but i still can't.

    But it's exactly like how u posted (i'm pretty sure i can follow that, haha)
    'cept that the 'yourtable' is a view.

    I ran the code in MS Access, and it was able to run. Except it did not include only one unique combinations of columns one two and three.
    So i guess I wouldn't be able to use it even if I got it to go in SQLServer

Posting Permissions

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