Re: "How do I ..." SQL question

[Harald Fuchs]

In article <8516270.1106005522722.JavaMail.root@stripped>,
zeus@stripped 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;