Hi,
I am trying to control which element gets picked when I do a group by,
but I can't figure out how to do it.
First some example data:
CREATE TABLE t ( id int not null primary key auto_increment, group_id
int not null, level int not null);
INSERT INTO t VALUES (1,1,2);
INSERT INTO t VALUES (2,1,3);
INSERT INTO t VALUES (3,1,1);
INSERT INTO t VALUES (4,2,1);
INSERT INTO t VALUES (5,2,1);
INSERT INTO t VALUES (6,2,1);
INSERT INTO t VALUES (7,4,4);
INSERT INTO t VALUES (8,1,1);
INSERT INTO t VALUES (9,2,2);
mysql> SELECT * from t;
+----+----------+-------+
| id | group_id | level |
+----+----------+-------+
| 1 | 1 | 2 |
| 2 | 1 | 3 |
| 3 | 1 | 1 |
| 4 | 2 | 1 |
| 5 | 2 | 1 |
| 6 | 2 | 1 |
| 7 | 4 | 4 |
| 8 | 1 | 1 |
| 9 | 2 | 2 |
+----+----------+-------+
9 rows in set (0.00 sec)
The real schema is of course much more complex.
I want to get one line for each "group_id" and it must be the one with
the lowest "level,id".
Adding "order by level" just orders the results when they are already
grouped.
mysql> select *,MIN(level) as min_level from t group by group_id
order by level,id;
+----+----------+-------+-----------+
| id | group_id | level | min_level |
+----+----------+-------+-----------+
| 4 | 2 | 1 | 1 |
| 1 | 1 | 2 | 1 |
| 7 | 4 | 4 | 4 |
+----+----------+-------+-----------+
In this case I wanted to get row 3 (with level 1) for group 1 for
example.
I think I understand why MySQL can't guess what I want in this case;
how can I explain it better in SQL? :-)
- ask
--
http://www.askbjoernhansen.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw
