From: | PFC <lists(at)boutiquenumerique(dot)com> |
---|---|
To: | zeus(at)ix(dot)netcom(dot)com, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: "How do I ..." SQL question |
Date: | 2005-01-18 00:13:13 |
Message-ID: | opskruwbvwth1vuj@musicbox |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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:
Is this :
SELECT DISTINCT place FROM a,(
SELECT count(*) AS count, name, year FROM a
GROUP BY name, year
ORDER BY count DESC, name ASC
LIMIT 4 OFFSET 1
) as foo WHERE name=foo.name AND year=foo.year
Problem with this approach is that you'll have to run the query twice,
one to get the hitlist by user, one for the places...
>
> 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.
>
> -Bob
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleus Mantzios | 2005-01-18 15:05:55 | mail + rfc822, rfc2822 + schema |
Previous Message | zeus | 2005-01-17 23:31:40 | "How do I ..." SQL question |