Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA10708 for ; Mon, 21 Jun 1999 19:30:34 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id TAA18339; Mon, 21 Jun 1999 19:29:21 -0400 (EDT) To: dgreer@websightsolutions.com cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] Trouble with massive select statement. In-reply-to: Your message of Mon, 21 Jun 1999 15:45:38 -0500 <99062116013106.13060@george> Date: Mon, 21 Jun 1999 19:29:21 -0400 Message-ID: <18337.930007761@sss.pgh.pa.us> From: Tom Lane Darren Greer writes: > Now what I need to do is select all the users who have taken a test > (for a particular station) within, say the last so many number of > days. The following select statement does that. > select distinct username, station > from testmaster t1 > where 1 < ( > select count(t2.username) > from testmaster t2 > where t2.test_date > '05-14-1999' > and t2.station = 'WZZZ' > and t1.username = t2.username > ) > ; The above doesn't seem to me to do what you claim you want to do --- it seems to be looking for users who have taken the test *more than once* in the given interval. Assuming that the code is right and the comment wrong ;-), I agree that this is the hard way to do it. The inner select will be re-evaluated from scratch for every tuple scanned by the outer select, so your run time is proportional to the square of the number of tuples in testmaster. Not good. I think you want to use a HAVING clause: SELECT username, station FROM testmaster WHERE test_date > '05-14-1999' and station = 'WZZZ' GROUP BY username, station HAVING count(*) > 1; The WHERE selects only the tuples you care about, the GROUP BY collects them into groups with the same username & station (thus serving the purpose you were using DISTINCT for; you don't need DISTINCT with GROUP BY); and finally the HAVING selects only the groups you care about, ie those containing more than one tuple. (When you use GROUP BY, count(*) and other aggregates only aggregate over a group, not the whole table.) regards, tom lane