PFC wrote: > 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
Thanks, this worked. As it happens, I am already creating a temporary table for the hitlist for other uses so that isn't a problem. -Bob ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
