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

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 (DOT) com> wrote