List:General Discussion« Previous MessageNext Message »
From:Jay J Date:January 22 2000 6:56am
Subject:Re: GROUP BY question
View as plain text  
----- 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)

Thread
Re: Re: forbidden charactersToboggan Hill22 Jan
  • Re: forbidden charactersStephen Johnson22 Jan
  • GROUP BY questionEric Peters22 Jan
  • Re: GROUP BY questionJay J22 Jan
    • Re: GROUP BY question - some more clarification/specsEric Peters22 Jan
  • Re: GROUP BY questionJay J22 Jan