[AccessD] Need Row Count in SQL for each Row

[Stuart McLachlan]

ROW_NUMBER is non-deterministic.

It is simply a sequential number for the recordset returned by a query.  It has no relationship 
to the sequence in which records were created. 

On 6 Apr 2017 at 12:13, Jim Dettman wrote:

> Brad,
> 
>  SELECT ROW_NUMBER() OVER(ORDER BY <field> ASC) as RowNum, <field>,
>  <another
> field> FROM <some table> WHERE....
> 
>   You'll have to run that as a pass through query though...JET/ACE
>   doesn't
> support ROW_NUMBER.
> 
>  If you can't do that, then your stuck with a VBA call.   Ordinarily
>  you'd
> use a sub-select, but you need a column to sequence things.
> 
> Jim.   
> 
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> Of Brad Marks Sent: Thursday, April 06, 2017 11:48 AM To:
> accessd at databaseadvisors.com Subject: [AccessD] Need Row Count in SQL
> for each Row
> 
> All,
> 
> In the 1970s, I worked with some mainframe database software from
> Cincom called "Total" which was later repackaged as "Supra".  Total
> and Supra were "network" databases which had a file type called
> "Variable" which stored records on "chains".  One of the downfalls of
> these databases was that the physical position of a record on a chain
> had meaning.  Often reading down a chain of records would indicate the
> chronological order of the records.
> 
> After leaving the world of Total and Supra, I thought that I would
> never again work with files where the physical position of the record
> held meaning.
> 
> I now face a similar challenge.  I am trying to read data from a file
> where the order of the records has meaning.  Unfortunately there is no
> field in the records that can be used to retain the order.
> 
> I have a simple SQL statement that pulls the data from this file.  I
> would like to add a column in the SQL to keep track of the order of
> the records for subsequent processing.  I know that I can do this via
> a function in Access.
> 
> Here is the catch.  I have been instructed to not use any "special"
> Access features in the SQL that are not available in "vanilla" SQL,
> because there is concern about being too locked into Access in case
> the shop moves to another platform.
> 
> Is there a way to add a column in an SQL statement to keep track of
> "record count" via vanilla SQL?
> 
> I have done some digging and have not found anything yet.
> 
> Thanks,
> Brad
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>