Обсуждение: "How do I ..." SQL question
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 2004kim south 2003kim south 2003bob west 2004bob west 2004bob west 2003joe south 2004joe south 2005sue west 2004bob east 2003joe east 2004joe east 2004sue south 2004bob north 2004bob 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 -------southwesteast 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
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 >
Hi there: Thank you for the response, which gave me what I wanted. Here is a follow-up question.. First a recap: 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 2004kim south 2003kim south 2003bob west 2004bob west 2004bob west 2003joe south 2004joe south 2005sue west 2004bob east 2003joe east 2004joe east 2004sue south 2004bob north 2004bob 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 joe 2005 1 kim 2004 Return only four rows beginning at second row into temporary table: CREATE TEMPORARY TABLE output AS SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC,name ASC LIMIT 4 OFFSET 1; SELECT * FROM output; 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 a.place FROM a, output WHERE a.name=output.name AND a.year=output.year; place -------southwesteast Here is the new question. I want to use the above result to update another table which contains unique places visited and also has a field to indicate recently visited places already present. SELECT * FROM places; refresh place --------- ------- 0 south 0 west 0 southwest (The following two queries do not work right and are what I need help with) Add new places: INSERT INTO places (refresh, place) SELECT DISTINCT 1, a.place FROM a, output LEFT JOIN places ON places.place=a.place WHERE a.name=output.name AND a.year=output.year AND places.place IS NULL; Update refresh flag for existing places. Note: the refresh field can have one of several values and I only want to change it when it has a particular value. UPDATE places SET refresh=1 FROM output, a WHERE places.refresh=0 AND places.place=a.place AND a.name=output.name ANDa.year=output.year; (The last query never updates the places tableand I'm not sure how to do this) I want this result: SELECT * FROM places; refresh place --------- ------- 1 south 1 west 0 southwest 1 east Any help appreciated. -Bob
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