>Hi all,
>
>Is it possible to create a counter column using select-sql? External functions allowed.
>

>* sample data
>create cursor test1 (fld1 i)
>insert into test1 values (1)
>insert into test1 values (2)
>insert into test1 values (3)
>create cursor test2 (fld1 i, cfld1 c(1))
>insert into test2 values (1,"A")
>insert into test2 values (1,"B")
>insert into test2 values (1,"C")
>insert into test2 values (2,"A")
>insert into test2 values (2,"B")
>insert into test2 values (3,"A")
>* desired result
>* fld1  cfld1  counter
>*    1      A        1
>*    1      B        2
>*    1      C        3
>*    2      A        1
>*    2      B        2
>*    3      A        1
>

You want the counter to reset every time fld1 changes, right? One problem with SELECT - SQL is that, for optimization purposes, there is no guarantee that the records will be selected in the desired order. Therefore, start by creating a cursor, and select from this cursor.

The function would increment a global variable, and restart when the group expression changes.

* Untested code
public gnFld1, gnCounter
gnFld1 = -1
gnCounter = 1

select Fld1, cFld1 from MyTable;
  order by Fld1, cFld1;
  into cursor Temp1;
  nofilter

select *, MyCounter(Fld1) as counter;
  from Temp1;
  into cursor Temp2

function MyCounter(tnFld1)
  if gnFld1 # tnFld1
    gnFld1 = tnFld1
    gnCounter = 0
  endif
  gnCounter = gnCounter + 1
  return gnCounter
endfunc

Another option would be to create a R/W cursor, and write a loop that goes through the values.

Finally, if you want all this for a report, it is much easier to use report variables.