>I have a table with the following structure and content:
>
>client
>____
>a
>a
>c
>c
>c
>c
>b
>b
>
>
>I would like get a query result that increments the count for
>each record within each client so that the results are:
>
>client Count
>______ _____
>a 1
>a 2
>c 1
>c 2
>c 3
>c 4
>b 1
>b 2
>
>Like I said, super easy to do within a loop. But is there an SQL trick to do the same thing without procedurual programming or triggers?
Another way, which might be faster since it avoids the non-equi-join:
SELECT client, maybesomesecondarysortfield FROM clients INTO CURSOR c1 ORDER BY 1,2 (or just 1 if there is no secondary sort field)
SELECT client, RECNO() AS countfield FROM c1 INTO CURSOR c2 ORDER BY 1,2 DESC
SELECT client, MIN(countfield) AS mincl FROM c2 INTO CURSOR cmin GROUP BY 1
SELECT c1.client, countfield - minc1 + 1 AS counted FROM c1 INNER JOIN cmin ON c1.client = cmin.client INTO CURSOR youresult ORDER BY 1,2
When testing these, sometimes it would complain about taking RECNO() of a cursor and recommend INTO TABLE, and sometimes not.