In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] writes:
> SELECT DISTINCT place FROM a ????;
> place
> -------
> south
> west
> east
> Note that the place north does not appear in the last result
> because north was only visited by bob in 2005 and kim in 2004,
> records which are not included in the limited result.
Using derived tables, this would be something like
SELECT DISTINCT a.place
FROM (
SELECT count(*) AS count, name, year
FROM a
GROUP BY name, year
ORDER BY count DESC, name ASC
LIMIT 4 OFFSET 1) AS d
JOIN a ON a.name = d.name AND a.year = d.year;
> I would like to be compatible with 3.23.xx.
The pre-4.1 workaround for derived tables are temporary tables:
CREATE TEMPORARY TABLE tmp AS
SELECT count(*) AS count, name, year
FROM a
GROUP BY name, year
ORDER BY count DESC, name ASC
LIMIT 4 OFFSET 1;
SELECT DISTINCT a.place
FROM tmp d
JOIN a ON a.name = d.name AND a.year = d.year;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
