From: Bob (BHockneyix.netcom.com)
Date: Tue Jan 18 2005 - 21:14:10 CST

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

> > Return only four rows beginning at second row:
> >
> > SELECT count(*) AS count, name, year FROM a
> > GROUP BY name, year
> > ORDER BY count DESC, name ASC
> > LIMIT 4 OFFSET 1;
> >
> > count name year
> > ------- ------ ------
> > 3 joe 2004 s,e,e
> > 2 bob 2003 w,e
> > 2 kim 2003 s,s
> > 2 sue 2004 s,w
> >
> > Select only places visited included in LIMITed query:
> >
> > SELECT DISTINCT place FROM a ????;
>
> Put the results of the LIMITed query into a temporary table and re-query.
>
>
> CREATE TEMPORARY TABLE tmpStep1 (
> freq int
> , name varchar(25)
> , year int
> );
>
> INSERT tmpStep1 (freq, name, year)
> 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 tmpStep1 ts1
> INNER JOIN a
> on a.name = ts1.name;
>
> Then you should get the list:
> >
> > place
> > -------
> > south
> > west
> > east

Thanks to all who responded. The inner join does what I wanted.

-Bob

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

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