Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

    Unanswered: Group supression

    I vaguely remember that MySQL has a feature for doing group suppression. I'm under a time crunch at the moment, does anyone know what the syntax is to invoke the group supression feature?

    Eample:
    Code:
    Col1  Col2             Col3
    A      2009-01-01   100
    A      2009-01-01   101
    A      2009-01-02   102
    B      2009-01-03   103
    B      2009-01-04   104
    C      2009-01-02   105
    Presents as:
    Code:
    A      2009-01-01   100
                        101
           2009-01-02   102
    B      2009-01-03   100
           2009-01-04   101
    C      2009-01-02   102
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan View Post
    I vaguely remember that MySQL has a feature for doing group suppression.
    i've never seen anything in mysql that can do that (not suggesting i know it all, i learned years ago not to make that misteak)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Pat, isn't this a job for your presentation layer?
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by gvee View Post
    Pat, isn't this a job for your presentation layer?
    In any relatively sane world, certainly group suppression belongs in the presentation layer. I'm dealing with a request from a manager, so sanity isn't part of this equation!

    MySQL often mixes database and presentation layer behaviors (see group_concat for an example). I remember someone showing me how MySQL could do group suppression in a result set but my mind screamed presentation layer so loudly that I can't remember how the feature worked.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    QMF forms for DB2 on the mainframe do a nice job of that,,, but as George says thats the presentation layer. I think there may be someway of doing something like this using recursive SQL, you might want to try the DB2 forum on this board and see if Tonkuma or Lenny can give you something, they seem to have a lot of time for playing with things better left elsewhere
    Dave

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I *guess* this could work
    Code:
    CREATE TEMPORARY TABLE meh (
       i        integer AUTO_INCREMENT
     , column_a char(1)
     , column_b datetime
     , column_c integer
    )
    
    INSERT INTO meh (column_a, column_b, column_c)
    SELECT Col1
         , Col2
         , Col3
    FROM   pats_table
    ORDER
        BY Col1
         , Col2
         , Col3
    
    SELECT NullIf(y.column_a, z.column_a) As Col1
         , NullIf(y.column_b, z.column_b) As Col2
         , y.column_c As Col3
    FROM   meh As y
     LEFT
      JOIN meh As z
        ON z.i - 1 = y.i
    Completely untested
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2006
    Posts
    56
    Hi,

    I just want to contribute here because I had to do exactly the opposite today. Someone inserted into a table "as is" from a Excel data sheet. As a result many rows in the DB had "ibid.".....!

    I just did group suppression for col2 but the same can be applied to the suppression of col1 too.

    Code:
    mysql> SELECT Col1
        ->      , CASE WHEN col2
        ->               = (SELECT col2
        ->                    FROM group_suppression tab2
        ->                   WHERE tab2.col1 = tab1.col1
        ->                     AND tab2.col2 = tab1.col2
        ->                     AND tab2.col3
        ->                            = (SELECT MAX(col3)
        ->                                 FROM group_suppression tab3
        ->                                WHERE tab3.col1 = tab1.col1
        ->                                  AND tab3.col3 < tab1.col3
        ->                               )
        ->                  )
        ->             THEN NULL
        ->             ELSE col2
        ->        END as "Col2"
        ->      , Col3
        ->   FROM group_suppression tab1;
    +------+------------+------+
    | Col1 | Col2       | Col3 |
    +------+------------+------+
    | A    | 2009-01-01 |  100 | 
    | A    | NULL       |  101 | 
    | A    | 2009-01-02 |  102 | 
    | B    | 2009-01-03 |  103 | 
    | B    | 2009-01-04 |  104 | 
    | C    | 2009-01-02 |  105 | 
    +------+------------+------+
    6 rows in set (0.00 sec)
    It was a case for case expressions.

Posting Permissions

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