Getting Row Number as a column

Question

I have the following query which works in SQL Server :-

query = "select * from (select *,ROW_NUMBER() OVER (ORDER BY LogDate) 
         AS ROW_NUM from table1) x where ROW_NUM>0";

But I want this in Access OLEDB, which doesn't support ROW_NUMBER() function.

What is another way to get ROW NUMBER in OLEDB Provider?

you can have a look on [How to Add RowNumber as a column while reading data from a csv file, using OleDb + C#](http://www.codeproject.com/Questions/263951/How-to-Add-RowNumber-as-a-column-while-reading-dat) — Mohit S, Nov 17 '15 at 06:03
You can create a temporary table with an identity column and then insert your records in to it as you select. This way you will be able to use the value in the identity column as the row number. — Kosala W, Nov 17 '15 at 06:12
Please see ["Should questions include “tags” in their titles?"](http://meta.stackexchange.com/questions/19190/should-questions-include-tags-in-their-titles), where the consensus is "no, they should not"! — , Nov 17 '15 at 09:07

score 0 · Answer 1 · edited May 23 '17 at 12:30

0

Access does not support rownum. You could add a column called ID which would use AutoIncrement to simulate a row number. But if a record is removed from the table the ID will not change, meaning a ID will be missing from your records.

This anwser also explores some possibilities: How to use the same function like Oracle Rownum in MS ACCESS

edited May 23 '17 at 12:30

Community

1
1

answered Nov 17 '15 at 08:57

Maiko Kingma

929
3
14
29

Getting Row Number as a column

1 Answers1