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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
