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/[EMAIL PROTECTED]
