Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle SQL query to check off and compare of previous record

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

From: andrewst <member14183_at_dbforums.com>
Date: Wed, 06 Aug 2003 14:57:43 +0000
Message-ID: <3208015.1060181863@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.

--
Posted via http://dbforums.com
Received on Wed Aug 06 2003 - 09:57:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US