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 a 'permission denied' error or similar.

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"


Yet another solution would be to follow the instructions in this post


It is about upgrading to a newer HSQL engine version instead of the one bundled with OpenOffice Base. New HSQL engine provides support for ROWNUM(), ROW_NUMBER() OVER, ANSI SQL constructs etc.


If you are trying to create a new column with consecutive numbers:

This works in calc, I missed that you are working in base

  1. Create new Column
  2. Type your starting number into the first cell (#1 in most cases)
  3. Exit the cell and return to the cell
  4. The cell will have a solid black border with a square black button in the bottom right hand corner
  5. Mouse click on this button and drag it as far down as you need to, to create all the numbers you need

I hope this is what you were looking for


  • True, that is a solution for a table in Calc. What I want here is to query the base table of names (which has an ID column that numbers items as they are added to the table) to produce a table in alphabetical order with the rows numbered consecutively. Your suggestion does have the makings of a workaround. of course.
    – Tony
    Jan 18 '13 at 16:34

Self-join with "<=" join condition and count() group by can do the job. The below queries return the same result.

SQL> select rownum, t.* from (select * from t order by b nulls first) t;

SQL> select count(t1.n) as rownum, t2.n, t2.a, t2.b from t t1, t t2 where nvl(t1.b,' ') < nvl(t2.b,' ') or nvl(t1.b,' ') = nvl(t2.b,' ') and t1.n <= t2.n group by t2.n, t2.a, t2.b order by 1 ;

ROWNUM          N A                              B

     1       1004 ddd
     2       1003 ccc                            --
     3       1002 bbb                            MM
     4       1001 aaa                            NN

4 rows selected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.