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

[Scott Baker]

Can't you do:

SELECT count(*) AS count, name, year FROM a
    WHERE place IN ('south','west','east')
    GROUP BY name, year
    ORDER BY count DESC, name ASC
    LIMIT 4 OFFSET 1;

zeus@stripped wrote:
> 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 ????;
> 
>  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
> 

-- 
Scott Baker
Canby Telephone - Network Administrator - RHCE
Ph: 503.266.8253