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.

dn.perl (AT) gmail (DOT) com wrote:
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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

dn.perl (AT) gmail (DOT) com wrote:
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:
HTH,
Ed

One more point which I have been curious about .
Why doesn't 'distinct' work on composite fields?

Table t1 : a1 number, a2 number.
Entries : (1,11) (2,21), (2,22) (2,22) (2,23) (2,23)
(3,33) (3, 33)

select distinct(a1, a2) from t1 ... does not work.
How do I write that query?

dn.perl (AT) gmail (DOT) com wrote:
Remove the parentheses:

select distinct a1, a2 from t1

Documentation: http://snipurl.com/uf81

Jeez, I tried out the query without parantheses, and it worked!!
This parantheses thing is a bit confusing.
create table t1(a1 number, a2 number)
create table t2(a1 number, a2 number)

insert into t2 select (a1, a2) from t1 : does not work.
Why? Does (a1, a2) return only one value (prob a1),
when the insert stmt expects 2 values?

And why does 'primary key' construct require parantheses
around the field even when the key is on just one field?
create table t1(aa number, primary key aa) : does not work.

On second thought the reason is not difficult to figure out
but sometimes it does get confusing as to why a simple
command is not working. Particularly the syntax requiring
absence of parantheses in : select distinct(aa, bb) from t1
is quite confusing.

dn.perl (AT) gmail (DOT) com wrote:
Do yo see a problem here?

SQL> select a1, a2 from t1;

A1 A2
---------- ----------
1 11
2 21
2 22
2 22
2 23
3 33
3 33

7 rows selected.

SQL> select distinct a1, a2 from t1;

A1 A2
---------- ----------
3 33
2 21
2 23
1 11
2 22

I don't.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org