Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2007
    Posts
    5

    Red face Unanswered: Distinct combination of 3 columns

    Hi All,
    I have to select eight columns from a table.
    the where condition should be the combination of three columns (out of those eight columns).
    i tried group by but with other five columns it wont work.

    its like
    select c1,c,c3,c4,c5,c6,c7,c8 from table where [ unique or distinct combination of c3,c4,c5 ]

    kindly let me know the where condition . DB2 version 7 or 8



    Thanks
    Ashok

  2. #2
    Join Date
    Jun 2004
    Posts
    115
    Hi

    How about

    select c1,c,c3,c4,c5,c6,c7,c8,
    row_number()
    over(partition by c3,c4,c5
    order by c3,c4,c5 nulls last)
    as rn
    from table
    where rn=1

    regards
    Hrishy

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I have no clue what you want to do. You want to return a row for each unique combination of c3, c4, and c5? What happens if there are multiple rows with that combination - which one do you pick? What happens if there is no row with that combination - you want to have this in the result or not?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select min(c1),max(c2)
         , c3,c4,c5
         , min(c6),max(c7),avg(c8)
     from table 
    group by c3,c4,c5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2007
    Posts
    5
    Quote Originally Posted by r937
    Code:
    select min(c1),max(c2)
         , c3,c4,c5
         , min(c6),max(c7),avg(c8)
     from table 
    group by c3,c4,c5

    I Cant use group functions in all other columns .

  6. #6
    Join Date
    Aug 2007
    Posts
    5
    Quote Originally Posted by db2hrishy
    Hi

    How about

    select c1,c,c3,c4,c5,c6,c7,c8,
    row_number()
    over(partition by c3,c4,c5
    order by c3,c4,c5 nulls last)
    as rn
    from table
    where rn=1

    regards
    Hrishy

    What is that row_number() and partition , do we have anything like that in DB2?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ashoash
    I Cant use group functions in all other columns .
    why not?

    i don't see anything about c1, c2 ... that makes them special
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Aug 2007
    Posts
    5
    Quote Originally Posted by stolze
    I have no clue what you want to do. You want to return a row for each unique combination of c3, c4, and c5? What happens if there are multiple rows with that combination - which one do you pick? What happens if there is no row with that combination - you want to have this in the result or not?
    ok , assume there are 8 rows with 8 columns (c3,c4,c5 are three columns in that 8). now i need to write a DB2 query to find out rows which have unique combination of C3,C4,C5 (distinct of 3 columns) , if there are 4 rows in that 8 rows which have same combination of c3,c4,c5 (same order) then the resultset should have only one row out of those 4 rows(in fact any row) + other 4 rows.

    how come there cud'nt be a resultset - there should be a result set .



    C3 C4 C5
    1 2 3
    2 3 1
    3 4 5
    2 3 1
    1 2 3
    5 6 7
    3 4 5
    7 6 5


    Ans - Resultset

    C3 C4 C5
    1 2 3
    2 3 1
    3 4 5
    5 6 7
    7 6 5

  9. #9
    Join Date
    Aug 2007
    Posts
    5
    Quote Originally Posted by r937
    why not?

    i don't see anything about c1, c2 ... that makes them special
    But i want to get the exact values and not the sum or avg or count
    Moreover Sum , count and Av will give you only one row.

    Example:

    C3 C4 C5
    1 2 3
    2 3 1
    3 4 5
    2 3 1
    1 2 3
    5 6 7
    3 4 5
    7 6 5


    Ans - Resultset

    C3 C4 C5
    1 2 3
    2 3 1
    3 4 5
    5 6 7

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you don't care which row, what's wrong with MIN values for the other columns?

    why? why? why?

    so far you have only talked about c1, c2, c3... -- this is way too generic for anybody to understand what you're really doing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2004
    Posts
    115
    Quote Originally Posted by ashoash
    What is that row_number() and partition , do we have anything like that in DB2?

    Hi

    row_number is a OLAP function.(there are many others available )

    Have a look at this function here
    http://www.ibm.com/developerworks/db.../0110lyle.html

    regards
    db2hrishy

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I'm seriously questioning the usability of such a scenario...

    Anyway, what you want to do is straight-forward:
    1. select all the unique combinations of the values in columns c2, c3, and c4
    2. select any row that has this combination


    Code:
    SELECT t2.*
    FROM   ( SELECT DISTINCT c2, c3, c4
             FROM ... ) AS t1,
           LATERAL ( SELECT *
                     FROM ...
                     WHERE  ( c2, c3, c4 ) = ( t1.c2, t1.c3, t1.c4 )
                     FETCH FIRST 1 ROW ONLY ) AS t2
    (The ROW_NUMBER() is more elegant.)

    how come there cud'nt be a resultset - there should be a result set .
    What do you mean with that? I said something completely different.

    Rudy, using column/aggregation functions is not an option because it wouldn't give you the values of a single row but rather the combination of multiple rows.

    p.s: There are no such things as "group functions".
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, knut, i understand about how the aggregate functions would not necessarily yield an actual row

    my question still stands: why is this not acceptable?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by r937
    yes, knut, i understand about how the aggregate functions would not necessarily yield an actual row

    my question still stands: why is this not acceptable?
    Good question: I had the same because the whole scenario doesn't make much sense to me.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  15. #15
    Join Date
    Dec 2005
    Posts
    273
    if the columns are CHAR-type, a construct like:

    SELECT c3,c4,c5,MIN( c1 !! c2 !! c6 !! c7 !! c8 )
    FROM table
    GROUP BY c3,c4,c5 ;

    might be useful.

    You get the values of ONE row for each distinct c3,c4,c5 combination.

Posting Permissions

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