Re: Oracle SQL query to check off and compare of previous record

> Is it possible to do an SQL looking at InvNumber and comparing the
> previous record to see if that previous record has the same invoice
> number and return the result 1 in the current line. For example:
>
> Invoice table
> Name InvNo
> ----- -----
> john 1
> bob 2
> jane 2
> ralph 3
> ken 4
> tom 4
>
> The result of the SQL would be
> Name InvNo Prevtrue
> ----- ----- --------
> john 1 0
> bob 2 0
> jane 2 1
> ralph 3 0
> ken 4 0
> tom 4 1
>
> Any posts or comments that can help in this matter would be
> appreciated.

This is assuming that records are to be sorted by name (yours aren't but the sort order must be defined).

DROP TABLE TEMP1; CREATE TABLE TEMP1
 (
  name VARCHAR2 (8),
  inv NUMBER
   ) ;

insert into temp1 values ('000dummy',0); -- this is a bit of a fudge but it makes the query simpler.

insert into temp1 values ('aaa',1);

insert into temp1 values ('aab',2);

insert into temp1 values ('aac',2);

insert into temp1 values ('aad',3);

insert into temp1 values ('aae',4);

insert into temp1 values ('aaf',4);

select w.name, w.inv, decode(sign(v.inv-w.inv),-1,0,1) as prevtrue from temp1 v, temp1 w
where (v.name, w.name) in
(
select x.name, min(y.name)
from temp1 x, temp1 y
where x.name < y.name
group by x.name
) Received on Thu Aug 07 2003 - 05:53:37 CDT