Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle SQL query to check off and compare of previous record
Originally posted by David B69
> 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.
>
> Thanks,
> Dave
Try the LAG function:
SQL> select ename, deptno, case when lag(deptno) over (order by ename) =
SQL> deptno then 1 else 0 end x
2 from emp
3* order by ename;
ENAME DEPTNO X
---------- ---------- ----------
ADAMS 20 0 ALLEN 30 0 BLAKE 30 1 CLARK 10 0 FORD 20 0 JAMES 30 0 JONES 20 0 KING 10 0 MARTIN 30 0 MILLER 10 0 SCOTT 20 0 SMITH 20 1 TURNER 30 0 WARD 30 1
14 rows selected.
-- Posted via http://dbforums.comReceived on Wed Aug 06 2003 - 09:57:43 CDT