>>>That was a tip by Tamar Granor on the last NY User Group meeting.
>>
>>Did Tamar show this exact code for this exact purpose? Is the use of RECNO() in SELECT - SQL guaranteed to work in this manner in VFP9?
>>
>
>Basically, we were going to skip this slide but I asked Tamar to stop on this problem as it sounded interesting. It was very quick so I don't really remember if she showed this tip in action or not. It should be in her book too.
>
>Hopefully she'll answer herself here later on.
>
>>In earlier versions of VFP I got burned with some very hard-to-track bugs when using RECNO() in SELECTs. There were significant SQL engine enhancements in VFP9; maybe it's now safe to use it that way. However, VFP9 also supports more complex queries in general, and without a lot of testing, how can we be assured that RECNO() will work as expected in these scenarios?
>>
>>Another consideration is that using RECNO() in a query is not ANSI-compliant. So, you couldn't use it to pull data off a non-VFP backend.
>
>That's for sure. In SQL Server you may use NextID trick, though I don't recall at the moment and need to search.

Here is some fun with RECNO():

* Test1
CLOSE DATABASES ALL

CREATE CURSOR Test1 ( Char1 C( 10 ) )
* We purposefully leave no rows in the first cursor

CREATE CURSOR Test2 ( Char1 C( 10 ), OrderCol I )
INSERT INTO Test2 ( Char1, OrderCol ) VALUES ( "One", 1 )
INSERT INTO Test2 ( Char1, OrderCol ) VALUES ( "Two", 2 )
INSERT INTO Test2 ( Char1, OrderCol ) VALUES ( "Three", 3 )
INSERT INTO Test2 ( Char1, OrderCol ) VALUES ( "Four", 4 )
INSERT INTO Test2 ( Char1, OrderCol ) VALUES ( "Five", 5 )

* Purposely set the currently selected table to 
* the first cursor instead of Test2 to see if we can fool RECNO():
SELECT Test1

SELECT ;
  * ;
  , RECNO( ) AS MyRecNo ;
  FROM ;
    ( SELECT ;
      * ;
      FROM Test2 ) DerivedTable ;
  INTO CURSOR Output ;
  ORDER BY OrderCol

* Result: RECNO() was not fooled!
Char1   OrderCol   MyRecNo
-----   --------   -------
One     1          1
Two     2          2
Three   3          3
Four    4          4
Five    5          5

Since that worked fine, let's make darned sure RECNO() can't get confused by any other table open in the current work area. Same as above, except we'll explicitly specify the derived table alias, "DerivedTable", in the RECNO() function:

* Test2
CLOSE DATABASES ALL

CREATE CURSOR Test1 ( Char1 C( 10 ) )
* We purposefully leave no rows in the first cursor

CREATE CURSOR Test2 ( Char1 C( 10 ), OrderCol I )
INSERT INTO Test2 ( Char1, OrderCol ) VALUES ( "One", 1 )
INSERT INTO Test2 ( Char1, OrderCol ) VALUES ( "Two", 2 )
INSERT INTO Test2 ( Char1, OrderCol ) VALUES ( "Three", 3 )
INSERT INTO Test2 ( Char1, OrderCol ) VALUES ( "Four", 4 )
INSERT INTO Test2 ( Char1, OrderCol ) VALUES ( "Five", 5 )

* Purposely set the currently selected table to 
* the first cursor instead of Test2 to see if we can fool RECNO():
SELECT Test1

SELECT ;
  * ;
  , RECNO( "DerivedTable" ) AS MyRecNo ;
  FROM ;
    ( SELECT ;
      * ;
      FROM Test2 ) DerivedTable ;
  INTO CURSOR Output ;
  ORDER BY OrderCol

* Result: Program error: "Alias is not found". But, we're explicitly referencing
the alias of the derived table! Shouldn't this work? < g >

Now, we get evil. We make sure there is a cursor named "DerivedTable" present before we run the SELECT:

* Test3
CLOSE DATABASES ALL

CREATE CURSOR DerivedTable ( Char1 C( 10 ) )
* We purposefully leave no rows in the first cursor

CREATE CURSOR Test2 ( Char1 C( 10 ), OrderCol I )
INSERT INTO Test2 ( Char1, OrderCol ) VALUES ( "One", 1 )
INSERT INTO Test2 ( Char1, OrderCol ) VALUES ( "Two", 2 )
INSERT INTO Test2 ( Char1, OrderCol ) VALUES ( "Three", 3 )
INSERT INTO Test2 ( Char1, OrderCol ) VALUES ( "Four", 4 )
INSERT INTO Test2 ( Char1, OrderCol ) VALUES ( "Five", 5 )

* Purposely set the currently selected table to 
* the first cursor instead of Test2 to see if we can fool RECNO():
SELECT DerivedTable 

SELECT ;
  * ;
  , RECNO( "DerivedTable" ) AS MyRecNo ;
  FROM ;
    ( SELECT ;
      * ;
      FROM Test2 ) DerivedTable ;
  INTO CURSOR Output ;
  ORDER BY OrderCol

* Result: not what we hoped for:
Char1   OrderCol   MyRecNo
-----   --------   -------
One     1          1
Two     2          1
Three   3          1
Four    4          1
Five    5          1

Yes, these examples are contrived. But it shows that, behind the scenes, there is different behaviour at least between RECNO() (with no arguments) and the version where an alias is specified. There is probably some sort of function overloading happening, but it shows that there can be significantly different behaviour between the overloads.