| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
11-30-09, 17:58
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,606
|
|
|
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.
|
|
11-30-09, 23:06
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
Quote:
Originally Posted by Pat Phelan
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)
|
|
12-01-09, 07:05
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
|
|
Pat, isn't this a job for your presentation layer? 
|
|
12-01-09, 12:13
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,606
|
|
Quote:
Originally Posted by gvee
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.
|
|
12-01-09, 12:14
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
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
|
|
12-01-09, 12:33
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
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 
|
|
03-30-10, 10:56
|
|
Registered User
|
|
Join Date: Mar 2006
Posts: 55
|
|
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. 
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Group supression
