From: Harald Fuchs (hf0722xprotecting.net)
Date: Tue Jan 18 2005 - 08:02:15 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

In article <8516270.1106005522722.JavaMail.rootdewey.psp.pas.earthlink.net>,
zeusix.netcom.com 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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]