Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

https://forum.openoffice.org/en/forum/viewtopic.php?f=83&t=61183

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.

share|improve this answer

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.

share|improve this answer

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.