From: operationsengineer1@yahoo.com
To: Richard Broersma Jr <rabroersma@yahoo.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: How To Exclude True Values
Date: Wed, 7 Jun 2006 12:25:15 -0700 (PDT)
Message-ID: <20060607192516.58324.qmail@web33303.mail.mud.yahoo.com> (raw)
In-Reply-To: <20060606234031.168.qmail@web31807.mail.mud.yahoo.com>
--- 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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: operationsengineer1@yahoo.com, rabroersma@yahoo.com
Subject: Re: How To Exclude True Values
In-Reply-To: <20060607192516.58324.qmail@web33303.mail.mud.yahoo.com>
* 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