pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: dgreer@websightsolutions.com
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Trouble with massive select statement.
Date: Mon, 21 Jun 1999 19:29:21 -0400
Message-ID: <18337.930007761@sss.pgh.pa.us> (raw)
In-Reply-To: <99062116013106.13060@george>

Darren Greer <dgreer@websightsolutions.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



view thread (5+ messages)  latest in thread

Message-ID: <18337.930007761@sss.pgh.pa.us>
Permalink:  ../18337.930007761@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/18337.930007761@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, dgreer@websightsolutions.com
  Subject: Re: [SQL] Trouble with massive select statement.
  In-Reply-To: <18337.930007761@sss.pgh.pa.us>
  References: <99062116013106.13060@george> <18337.930007761@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox