SQL qn:- comparing data in rows

Question

hi,

i would like to compare numeric data in rows. for eg, i have a table that has a column as such:-

Number
======
 1.88
 9.99
 8.76
 9.88

I want to compare 2nd value, 3rd value, 4th value to the 1st value. And then 3rd, 4th value to the 2nd. then 4th to 3rd.

How can i construct an sql to do this?

Answer 1

Not sure of the utility of this but maybe that is what you are looking for... An easier way to compare values is to use the ORDER BY clause.

I took the liberty of adding an id column to the table which I call tblTst. This is convenient to select some specific values (see the WHERE id in (...) clause), and also it is used to only avoid duplicates in the cross product. To compare the whole table, simply omit the WHERE clause. The join condition can be changed to strictly less than (<) to prevent comparing values with themselves (was just convenient to test my '=')

With 100 values in the table the bottom (or top) triangle of the matrix will include 5,000 comparisons. (again, I'm puzzled as to how this can be useful or fun...)

select T1.value AS Operand1,
     CASE WHEN T1.Value < T2.Value THEN '<'
          WHEN T1.Value = T2.Value Then '='
          ELSE '>'
     END AS Comp,
     T2.Value AS Operand2
FROM tblTst T1
JOIN tblTst T2 ON T1.id <= T2.id
WHERE T1.id in (1,2,3,4)

Sample output     
Operand1 Comp  Operand2
1.88    = 1.88
1.88    < 9.99
1.88    < 8.76
1.88    < 9.88
9.99    = 9.99
9.99    > 8.76
9.99    > 9.88
8.76    = 8.76
8.76    < 9.88
9.88    = 9.88

Answer 2

You need to have some way of assigning sequence numbers to the rows.

Consider the following table:

seq  number
---  ------
  1    1.88
  2    9.99
  3    8.76
  4    9.88

created with:

drop table a;
commit;
create table a (seq integer,num float);                                     
insert into a (seq,num) values (1,1.88);
insert into a (seq,num) values (2,9.99);
insert into a (seq,num) values (3,8.76);
insert into a (seq,num) values (4,9.88);
commit;

You can then execute:

select
    ta.seq as aseq,
    tb.seq as bseq,
    ta.num as anum,
    tb.num as bnum,
    ta.num - tb.num as diff
from a ta, a tb
where tb.seq > ta.seq;

which will give you:

aseq  bseq  anum  bnum   diff
----  ----  ----  ----  -----
   1     2  1.88  9.99  -8.11
   1     3  1.88  8.76  -6.88
   1     4  1.88  9.88  -8.00
   2     3  9.99  8.76   1.23
   2     4  9.99  9.88   0.11
   3     4  8.76  9.88  -1.12