Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

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]

share|improve this question

2 Answers 2

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"

share|improve this answer

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

Wilhelm

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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