Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: newbie : writing an efficient query

Re: newbie : writing an efficient query

From: Ed Prochak <edprochak_at_gmail.com>
Date: 4 Aug 2006 11:35:24 -0700
Message-ID: <1154716524.060311.324950@m73g2000cwd.googlegroups.com>

dn.perl_at_gmail.com wrote:
> I have a table t1 : time1 unique, qty
> Entries : (1, 10) (2, 20) (3,30) (32,20)
>
> I want to find out the time1 values where qty is the same.
>
> select a1.time1, a2.time1
> from t1 a1, t1 a2
> where a1.qty = a2.qty and a1.time1 <> a2.time1 -- and a1.time1 >
> a2.time1
>
> produces output :
> -------
> 32 2
> 2 32
>
> But I want just one line in the output. Uncommenting the clause
> achieves the effect but somehow it seems a contrived way of
> getting the desired result. Besides if unique key is on (date + time),
> it would make the task more difficult. I think a more logical way
> would help me write the query efficiently whether the unique
> key was on a single field or multiple fields.
>
> Please advise about a logical way of writing the query.

It is not contrived. it enforces the rule that time runs forward. It gets you the right result
so put it back in and remove the <> condition like this:
> select a1.time1, a2.time1
> from t1 a1, t1 a2
> where a1.qty = a2.qty and a1.time1 > a2.time1 ;

HTH,
   Ed Received on Fri Aug 04 2006 - 13:35:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US