Re: SQL Blues.....

On Wed, 5 Jul 2000 21:35:23 +0200, "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:

>add
>and catch_entries.actual_points =
>(select max(actual_points
> from catch_entries ce1
> where ce1.pin = catch_entries.pin)
>

Just a little trouble with the max(). The select max() will be evaluated for each record of catch_entries and the use of Max()/min() involve full scan of the table. It's easy to understand that if catch_entries is a big table, the performance could be very bad.
There is another way to do it
add
and not exists (select 'x' from catch_entries ce1

                where ce1.pin = catch_entries.pin
                  and cel.actual_points > catch_entries.actual_points)

The same can be done with a < for a min().

-- 
Luc Bosman
DEV/2000 Software Engineer
Database and Network Administrator
l.bosman_at_wanadoo.be
ICQ 37011625          http://users.skynet.be/kobukai/
 ---------------------------------------------------- 
|  -°)                                          (°-  |
|  /\\  Linux the choice of the GNU generation  //\  |
| _\_v                                          v_/_ |
 ----------------------------------------------------