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. -Bob ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
