Show row number of records in query result

[nebergall]

How do I include a column of row numbers in a query result?
The query already exists and I assume I could add another column and associate it with the table I want the rows# from, then enter the appropriate SQL. How do I generate the row number column? Thx, Erik

[fpy]

Ask/Guide - How to use the Ask site - The Document Foundation Wiki? #Are_there_any_answers_to_my_question_yet?

Number query row in Firebird libreoffice Base

Hello, Use this SQL: SELECT "Planet", "Moon", ROW_NUMBER() OVER (ORDER BY ID) AS "Row Number" FROM "Planets_And_Moon" WHERE "Moon" = 'Yes' See more on this in the Firebird documentation found here → Firebird RDBMS. Firebird 2.5 Language Reference (English) - this is latest.

Creating a column in a query that displays the row number

Your first “problem” is Base not being a database, but a frontend to databases. Two embedded databases are usually available. . I just assume you use/test with the “default”, wich is/was most times HSQLDB V1 8 Sadly this Version included no window-functions like row_number(). . For a suggestion see sql - HyperSQL partitioned row_number() equivalent - Stack Overflow

How to set an item in a query to To Row number

If you already are doing a macro, this is simple enough. Simply calculate the value in the macro and pass it to an UPDATE (or INSERT) query in a loop. Here is an example for “Table1” where “priKey” is the primary key and “criteriaA” is common to all of the rows that you wish to re-number. Sub sqlOperation REM #! create two cursors: one to read recordset, other to execute updates oConn = ThisDatabaseDocument.DataSource.getConnection("","") oQueryResultSet = oConn.CreateStatement() oQueryUpda…

[nebergall]

Sorry, the suggestion is confusing and not understandable.

[cpb]

a row number is dependant on the queries ORDER BY clause and method of calculation is determined by the database you use, you have provided neither.
.
if you tell us the name of the database used
and
post a sample of your query in SQL format or better still upload a test database
then
it may be possible for someone to provide an answer.
.
fpy posted a link to the Ask Guide, follow the guide and save everyone the angst.

[Wanderer]

@nebergall

Sorry, the suggestion is confusing and not understandable.

@fpy linked several threads, as you have not told, wich database you use… So we can not give a precise answer.
From the thread below we can see you are using HSQL-embedded, but this also tells you have to use a more complex way to create row-numbers.

Reset Primary Key

My odb says HSQLDB Embedded. The solutions provided seen laborious and not worth the risk of screwing up one or more related tables and any of my hard-entered data. I appreciate taking the time to detail the process, though. That said, when I look at a typical table, the first primary key starts with the number 1, not zero. Should a table start with record 0 or is the 0 somehow consumed by the table and the first record should be 1? Also, as I mentioned in my initial question, my subform pr…