Re: reset sequence / index (ACCESS)

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: Thu, 09 Dec 2004 08:22:14 GMT
Message-ID: <WWTtd.65436$K7.22319@news-server.bigpond.net.au>

A solution would be:
SELECT

      accnt,
      COUNT(*)
         OVER (
            PARTITION BY accnt
            ORDER BY rownum
            RANGE UNBOUNDED PRECEDING
      ) AS accnt_index
   FROM
      your_table_name

/

This solution is based upon the example given on pp.560-561 of "Expert One-on-One(tm) Oracle" by Thomas Kyte (A-Press:2001).

BTW, the name, "index", is a reserved word and should not be used as a column name.

The key to the solution is the OVER clause. Here the PARTITION BY sub-clause causes the reseting of the ACCNT_INDEX column. The COUNT(*) gives the number of rows encountered so far in the order produced by the ORDER BY sub-clause. (In the absence of any other information in your post, I had to resort to using the pseudo-column, ROWNUM, and risk incurring the wrath of other DBAs.) The RANGE UNBOUNDED PRECEDING causes the COUNT(*) to evaluated for all rows encountered so far in the partition.

Douglas Hawthorne

<sbowles_at_lwcky.com> wrote in message
news:1102522760.919739.3790_at_c13g2000cwb.googlegroups.com...

>I have a field (acct) that I want to create an index on.  The index

> field needs to be reset to 1 everytime the acct changes. I have mainly
> only built queries and tables and have limited SQL and VBA knowledge.
> Thanks
> accnt index
> 101 1
> 101 2
> 101 3
> 102 1
> 102 2
> 103 1
>
Received on Thu Dec 09 2004 - 02:22:14 CST