Get Row Number in Access Query

Question

How would I go about fetching the row number (or index) of a particular row that matches a condition?

Example:

If I wanted to select:

SELECT * FROM tblAccount WHERE [Account ID] = 2343 LIMIT 0, 1

How would I get the row number of that selected row?

Thanks.

Related thread - http://stackoverflow.com/questions/1041792/ms-access-row-number-specify-an-index — KV Prajapati, May 11 '12 at 07:51
You want the physical row number of that record in table, or you want dynamic row number 1..n for your result. — Romil Kumar Jain, May 11 '12 at 07:52
Please provide more information. Relational database tables do not have row numbers. The "position" of a row is determined by the sort order. Also, you have tagged MS Access but posted a query that will not run in MS Access. — Fionnuala, May 11 '12 at 08:49

score 0 · Answer 1 · answered May 11 '12 at 08:01

Row number isn't a property of a SQL-query result, other than the key - but I guess that's not what you are after. If you need it in the access-table, then you have to create and maintain it as a column in the table.

After you fetch the result into a DataTable, then you can find the DataTable-specific row number using the Select and IndexOf methods.

Can you provide more information about what you want to use it for?

score 0 · Answer 2 · answered May 11 '12 at 08:05

Look at the code below

OleDbConnection cn = new OleDbConnection();
OleDbCommand cmd = new OleDbCommand();
DataTable schemaTable; 
OleDbDataReader myReader; 

//Open a connection to the SQL Server Northwind database.
cn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;User ID=login;
                       Password=password;Initial Catalog=Northwind";
cn.Open();

//Retrieve records from the Employees table into a DataReader.
cmd.Connection = cn;
cmd.CommandText = "SELECT * FROM tblAccount WHERE [Account ID] = 2343 LIMIT 0, 1";
myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo); 

//Retrieve column schema into a DataTable.
schemaTable = myReader.GetSchemaTable();
...

schemaTable will tell you everything like schemaTable.Column.Count to tell column number

Get Row Number in Access Query

2 Answers2