Hi,

I would like to produce calculated (in SQL SELECT) column with output
row number.

Please look on following code:

**
private counter
counter=0

SELECT myIncrement(@counter) as No, * from some_table


function myIncrement
parameters tnCounter
tnCouter=tnCouter+1
return tnCouter

Query result looks like this:

No Field
--------
2 .....
3 .....
4 .....
5 .....
... .....

'No' always starts from 2 (in cases I tested). Problem is that I do not know
is this a rule that function is called n+1 times and I can correct this by
SELECT myIncrement(@counter)-1 as No - or should I try to find other
solution.


Best Regards,
Iks

Iks,

VFP always runs the query for the first record to determine the field types and sizes to create the cursor properly (unless it's strictly a filtered "view"), before it does the real SELECT. About all you can do in this case is to start "counter" at -1!

Rick

"Iks" <oopsik (AT) polbox (DOT) com> wrote

"Iks" <oopsik (AT) polbox (DOT) com> schrieb im Newsbeitrag
news:bu16q2$24bl$1 (AT) news2 (DOT) ipartners.pl...
Hello,
Alternatively you can get it by doing a self-join, to
avoid the custom function (or is it a "cross join"?)

hth
-Stefan

* sql_dynamic_rank.prg
CREATE CURSOR testtable (cName C(10), nNumber Int)
INSERT INTO testtable VALUES ("James",19)
INSERT INTO testtable VALUES ("Adam",5)
INSERT INTO testtable VALUES ("Richard",1)

SELECT count(*) as rank, ;
T1.cName, T1.nNumber ;
FROM testtable T1 ;
JOIN testtable T2 ;
on T1.nNumber >= T2.nNumber ;
GROUP BY 2, 3 ;
ORDER BY 1 ;
INTO CURSOR resultByNum
BROWSE LAST NOWAIT

SELECT count(*) as rank, ;
T1.cName, T1.nNumber ;
FROM testtable T1 ;
JOIN testtable T2 ;
on T1.cName >= T2.cName ;
GROUP BY 2, 3 ;
ORDER BY 1 ;
INTO CURSOR resultByChar
BROWSE LAST NOWAIT
* eop