DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=wdOpLAjWoxaxuGyy5DwvY182xLv5GeS5BzVH9dY/T6T81NlJ1MTpUXSrAaRjIEqFdPaxi7S0BGLzNRSDoafS4WGnjrwqrXT862T9v24giD7hQDM8GFrELBcv6+HjXD4wSzXPm9oqP4+1R7wN8c/dDM4xJDyLJKKXlH6yybXVKyw=
	;
Message-ID: <20060607192516.58324.qmail@web33303.mail.mud.yahoo.com>
Date: Wed, 7 Jun 2006 12:25:15 -0700 (PDT)
From: <operationsengineer1@yahoo.com>
Subject: Re: How To Exclude True Values
To: Richard Broersma Jr <rabroersma@yahoo.com>,
	"pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
In-Reply-To: <20060606234031.168.qmail@web31807.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

--- Richard Broersma Jr <rabroersma@yahoo.com> wrote:

> > Richard, that is the result i would need given
> that
> > data set.  i have to digest this version, though.
> > 
> > should this query be more efficient than the
> subquery
> > version as the table starts to get large?
> 
> My experience is that Distinct On queries do not
> preform as well as their group by counter parts. 
> I believe that others have also commented to the
> same effect.
> 
> To speed the query you could apply indexes on the
> group by fields and the join columns.
> 
> Also, I produced a second query using PostgreSQL:
> 
> select a.id_i, a.ir_id, a.test, a.stamp
> from test a 
> 
> join
> (
> select max(stamp) as mstamp, id_i
> from test
> group by id_i
> ) b
> 
> on a.stamp = b.mstamp
> 
> where a.test = false
> ;
> 
> -- result
> 
>  id_i | ir_id | test |        stamp
> ------+-------+------+---------------------
>     4 |     8 | f    | 2006-06-05 08:00:00

Richard,

given the following table structure...

t_inspect
=========
inspect_id
...

t_inspect_result
================
inspect_result_id
inspect_id (fkey)
inspect_result_pass (bool) -- indicates fail or pass
inspect_result_timestamp

the following query...

select a.inspect_id, a.inspect_result_id, 
       a.inspect_result_pass, 
       a.inspect_result_timestamp
from t_inspect_result a 

join
(
select max(t_inspect_result.inspect_result_timestamp) 
       as mstamp, 
       t_inspect_result.inspect_id
from t_inspect_result
group by t_inspect_result.inspect_id
) b

on a.inspect_result_timestamp = b.mstamp

yields the following error:

ERROR:  schema "a" does not exist

i tried to interpret you query and apply it to my
case, but, apparently, i didn't too good of a job.

do you see the error?

tia...

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com