[EMAIL PROTECTED] wrote on 01/17/2005 06:45:22 PM:
> Hi there:
>
> I have a "How do I..." SQL question regarding selecting
> distinct values from a field not included in an aggregated
> query when LIMIT is in effect, illustrated by the
> following example:
>
> Table a contains the names of individuals, the places
> they have visited and the year in which they were visited.
>
> Let's see who has visited where and when:
>
> SELECT * FROM a;
>
> name place year
> ------ ------- ------
> kim north 2004
> kim south 2003
> kim south 2003
> bob west 2004
> bob west 2004
> bob west 2003
> joe south 2004
> joe south 2005
> sue west 2004
> bob east 2003
> joe east 2004
> joe east 2004
> sue south 2004
> bob north 2004
> bob north 2005
>
> Summarize data by number of places visited by year:
>
> SELECT count(*) AS count, name, year FROM a
> GROUP BY name, year
> ORDER BY count DESC, name ASC;
>
> count name year
> ------- ------ ------
> 3 bob 2004
> 3 joe 2004
> 2 bob 2003
> 2 kim 2003
> 2 sue 2004
> 1 bob 2005
> 1 kim 2004
> 1 joe 2005
>
> 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
>
> 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.
>
> Any help appreciated.
>
> I would like to be compatible with 3.23.xx.
>
> -Bob
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
Basically, if you need to treat a set of results as source data, your best
option is to make a table (temporary or permanent) out of your results.
Then when you are through, cleanup for the next time.
DROP TEMPORARY TABLE tmpStep1;
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
