Re: newbie : writing an efficient query

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.

Look at using DECODE or CASE to perform crosstabulation.

Demo in Morgan's Library at www.psoug.org under DECODE.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org