----- Original Message -----
From: "Eric Peters" <eric@stripped>
To: <mysql@stripped>
Sent: Saturday, January 22, 2000 12:20 AM
Subject: GROUP BY question
> I'm trying to figure out if I can have something like
>
> SELECT * FROM Foo GROUP BY (Column1,Column2),Column3
>
> type of thing where It would group by the unique column 1,2 and then
finally go
> ahead and basically pull that result and do a group by column3 rather than
the
> a group of the 3 unique columns
>
> Thanks for your time
>
> Eric
I'll bite.
It's kinda ugly, but hey .. it works.
DROP TABLE IF EXISTS foo2;
CREATE TABLE foo2 (id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
column1 CHAR(255), column2 CHAR(255), column3 CHAR(255));
explain foo2;
insert into foo2 (column1, column2, column3) values (1,1,1), (1,1,2),
(1,2,2), (2,2,2);
SELECT *, CONCAT(CONCAT(Column1, Column2), Column3) AS GB FROM foo2 GROUP BY
GB;
insert into foo2 (column1, column2, column3) values (1,1,1), (1,1,2),
(1,2,2), (2,2,2);
SELECT *, CONCAT(CONCAT(Column1, Column2), Column3) AS GB FROM foo2 GROUP BY
GB;
-Jay J
-----------------------------------------------
mysql> CREATE TABLE foo2 (id INT UNSIGNED NOT NULL PRIMARY KEY
AUTO_INCREMENT, column1 CHAR(255), column2 CHAR(255), column3 CHAR(255));
Query OK, 0 rows affected (0.06 sec)
mysql> explain foo2;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | | PRI | 0 | auto_increment |
| column1 | char(255) | YES | | NULL | |
| column2 | char(255) | YES | | NULL | |
| column3 | char(255) | YES | | NULL | |
+---------+------------------+------+-----+---------+----------------+
4 rows in set (0.18 sec)
mysql> insert into foo2 (column1, column2, column3) values (1,1,1), (1,1,2),
(1,2,2), (2,2,2);
Query OK, 4 rows affected (0.09 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT *, CONCAT(CONCAT(Column1, Column2), Column3) AS GB FROM foo2
GROUP BY GB;
+----+---------+---------+---------+------+
| id | column1 | column2 | column3 | GB |
+----+---------+---------+---------+------+
| 1 | 1 | 1 | 1 | 111 |
| 2 | 1 | 1 | 2 | 112 |
| 3 | 1 | 2 | 2 | 122 |
| 4 | 2 | 2 | 2 | 222 |
+----+---------+---------+---------+------+
4 rows in set (0.21 sec)
mysql> insert into foo2 (column1, column2, column3) values (1,1,1), (1,1,2),
(1,2,2), (2,2,2);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT *, CONCAT(CONCAT(Column1, Column2), Column3) AS GB FROM foo2
GROUP BY GB;
+----+---------+---------+---------+------+
| id | column1 | column2 | column3 | GB |
+----+---------+---------+---------+------+
| 1 | 1 | 1 | 1 | 111 |
| 2 | 1 | 1 | 2 | 112 |
| 3 | 1 | 2 | 2 | 122 |
| 4 | 2 | 2 | 2 | 222 |
+----+---------+---------+---------+------+
4 rows in set (0.08 sec)
mysql> # wooohoo!
mysql> SELECT *, CONCAT(CONCAT(Column1, Column2), Column3) AS GB FROM foo2
GROUP BY GB ORDER BY GB DESC;
+----+---------+---------+---------+------+
| id | column1 | column2 | column3 | GB |
+----+---------+---------+---------+------+
| 4 | 2 | 2 | 2 | 222 |
| 3 | 1 | 2 | 2 | 122 |
| 2 | 1 | 1 | 2 | 112 |
| 1 | 1 | 1 | 1 | 111 |
+----+---------+---------+---------+------+
4 rows in set (0.02 sec)
