Re: How can I know how many same orderno is shown in rows sequentially?

From: Jan Krueger <jk_at_stud.uni-hannover.de>
Date: Mon, 08 Oct 2007 08:40:35 +0200
Message-ID: <4709d04a$0$29375$4c56b896@news-read1.lambdanet.net>

deneb wrote:
> hi there,
>
> I have a table that has below columns and rows.
>
>
> DAY SEQ ORDERNO
> -----------------------------------------------------------------
> 20071002 1 LM001
> 20071002 2 LM001
> 20071002 3 LM003
> 20071002 4 LM003
> 20071002 5 LM005
> 20071002 6 LM001
> 20071002 7 LM002
> 20071002 8 LM002
> 20071002 9 LM002
> 20071002 10 LM001
> 20071002 11 LM001
> 20071002 12 LM004
> 20071002 13 LM004
>
>
> How can I get a datas like this ?
>
>
> ORDERNO COUNT
> --------------------------------------------
> LM001 2
> LM003 2
> LM005 1
> LM001 1
> LM002 3
> LM001 2
> LM004 2
>
>
> That is, how can I know how many same orderno is shown in rows
> sequentially?
> I'm using oracle 9i. PL-SQL statements could be used.

I don't have a solution at hand, but I would suggest to look into the documentation for analytic functions. (Somewhere in the Datawarehousing). They support a so called sliding window. Should be solveable by SQL. Otherwise, the way to get this result in a loop in PL/SQL is pretty obvious in my opinion.

Jan Received on Mon Oct 08 2007 - 01:40:35 CDT