Re: [SQL] Trouble with massive select statement.

Darren Greer <dgreer(at)websightsolutions(dot)com> 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