I am trying to help someone who wants to put data into alphabetical order with a row number. I thought I could do a query like this
select “name”, ROWNUM() as “Row” from “data” order by “name”
But I just get an error.
Can anyone help? Does ROWNUM() work the way I think it should? Or is there a better way to get a row number on the data ordered by name?
Here is the error I am getting:
Access is denied: ROWNUM in statement [SELECT “Description”, ROWNUM( ) FROM “Data” ORDER BY “Description” ASC]
I now have a solution using Tools > SQL…
I start with the unsorted “Data” table. For me it is just a table with auto-generated “ID” and a column “Name”. Here it is
ID Name
0 John
1 Julian
2 Carol
3 Anthony
4 Paul
5 Chris
6 Bob
I then create a new table “Sorted”. This just has a single column “Name”. I create it in design view and when I save it I do not create a Primary Key.
Then using Tools > SQL… I run these commands
Insert into “Sorted” select “Name” from “Data” order by “Name” Asc;
Alter Table “Sorted” add column “Item” INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) before “Name”
I get this
Item Name
1 Anthony
2 Bob
3 Carol
4 Chris
5 John
6 Julian
7 Paul
which is what I want.
If I add more rows to the “Data” table, and want to recreate the “Sorted” table, I need to run these SQL commands
Delete from “Sorted”;
Alter Table “Sorted” drop column “Item”;
Insert into “Sorted” select “Name” from “Data” order by “Name” Asc;
Alter Table “Sorted” add column “Item” INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) before “Name”