List:General Discussion« Previous MessageNext Message »
From:Felix Geerinckx Date:December 29 2005 1:10pm
Subject:Re: LIMIT on GROUP BY?
View as plain text  
On 28/12/2005, SGreen@stripped 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
Thread
LIMIT on GROUP BY?Jay Paulson \(CE CEN\)28 Dec
  • Re: LIMIT on GROUP BY?Peter Brawley28 Dec
  • Re: LIMIT on GROUP BY?SGreen28 Dec
  • Re: LIMIT on GROUP BY?Felix Geerinckx29 Dec