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

Andrew, thank you so much, you are a lifesaver. That's excellent.

Dave

andrewst <member14183_at_dbforums.com> wrote in message news:<3208015.1060181863_at_dbforums.com>...
> 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.
Received on Thu Aug 07 2003 - 09:32:31 CDT