On 28/12/2005, [EMAIL PROTECTED] wrote:
> I don't think there is any way with plain-old SQL (extended or
> otherwise) to do it in a single statement (unless you are doing it
> iteratively - that is: in a stored procedure and row-by-row).
USE test;
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
i TINYINT UNSIGNED NOT NULL,
c CHAR(1) NOT NULL,
PRIMARY KEY (i, c)
);
INSERT INTO foo VALUES
(1, 'a'), (1, 'b'), (1, 'c'), (1, 'd'),
(2, 'x'), (2, 'y'), (2, 'z');
SELECT DISTINCT
f1.i,
(SELECT GROUP_CONCAT(f2.c)
FROM foo f2
WHERE
f2.i = f1.i AND
2 > (SELECT COUNT(*)
FROM foo f3
WHERE
f3.i = f2.i AND
f3.c < f2.c
)
) cc
FROM foo f1;
Probably not useful in production code for performance reasons, and one
should be careful with duplicate rows (not allowed in this example).
--
felix
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
