[Solved] Row_Number()

[silvrwood]

In Base, is there a way to perform a row_number function similar to the one described at http://www.sqlserver.info/syntax/row_nu ... to-use-it/?

[keme]

Two suggestions from StackOverflow.
(Three if you count the suggestion to use an external database server.)

I haven't tried, but on first look there seem to be pitfalls.
One solution depends on you knowing how high the count can go, and make as many query levels as required. That would work for serializing in small groups like requested in that thread.
The other solution may depend on data not being duplicated (no ties, as it were). Using key field for secondary sort would handle that, I suspect, but you have to test it properly.

What do you need the row-numbering for? Other workarounds may arise if we know more about context.

[Villeroy]

Put a copy of your Base document with embedded HSQL1.8 in a dedicated directory.

Add a driver directory with a hsqldb.jar of version 2.3.4 which is is the version which is the best compatible with Base.
https://sourceforge.net/projects/hsqldb ... sqldb_2_3/

Save a Python script of mine in the Script/python/ folder of your user profile or share/Script/python of your office installation path.
viewtopic.php?f=21&t=86071

Open your Base document and run the main routine of that macro script (menu:Tools>Macros>Organize>Python...)

Test your database document and if it works properly, save it. It is connected to a set of files in a database/ subdirectory driven by the HSQL2 driver in the driver subdirectory.
Your HSQL package comes with full documentation. Chapter 10 of the HSQL user guide lists all the availlable functions, including ROWNUM().

[Sliderule]

silvrwood:

OpenOffice / LibreOffice Base is NOT a database. Rather, it is a front-end for whatever database back-end you choose to use. While the 'default' for an Embedded Database ( where the database contents / is HSQL Version 1.8.0.10 ), a very OLD version of HSQL.

The current release of HSQL should you choose to use it ( I do, and, it is my preferred database back-end because it is SQL compliant ) is HSQL 2.4.1 as of the date I am writing this. HSQL can be downloaded from:

HSQLDB - 100% Java Database: http://hsqldb.org/

While running HSQL Version 2.4.1 in combination with OpenOffice / LibreOffice Base gives you some of the following advantages over just the old HSQL Version 1.8.0.10 that is part of the Embedded Database:

1. Many more functions available, including:
   1. ROWNUM() to include the result set row number you are looking for
   2. Date arithmetic, such as DATEADD()
   3. GROUP_CONCAT
   4. MEDIAN
   5. Write your own functions for use with the database
2. Ability to use other OUTSIDE database tools with your database engine . . . such as . . .
   1. SqlTool.jar: sqltool.jar
   2. SQL Workbench/J: http://www.sql-workbench.net/
   3. SQuirel SQL Client ( jdbc ): http://www.squirrelsql.org/
   4. HSQL Database Manager / HSQL Database Manager Swing: hsqldb.jar
3. Since the database content is NOT a part of the *.odb file, when it corrupts ( and it will ) . . . you are out of luck. However, if the database engine is not a part of the *.odb file, you will be able keep going.
4. You can run your database with multiple users ( as a Server )
5. Error messages that are user friendly ( whether with Base Forms, or, when using the database engine External Tools ) can be added . . . withOUT macros . . . and be consistent for all users because it is part of the database engine rather than only Base
6. Import and export text files to your database engine using SqlTool.jar
7. Create flexible HTML reports using SqlTool.jar

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[silvrwood]

Thanks, Sliderule. That’s just what I needed to know.

Thank you to everyone for responding.

[eremmel]

You can simulate this with HSQLDB 1.8. This is based on a unique ordering. The simple way is no partitioning. Assume you have table Table1 and the ordering is on field Field1. You can write.

Code: Select all

 SELECT COUNT(T2."Field1") AS "RowNum", T1."Field1",  T1."Field2",  T1."Field3", ...
FROM "Table1" T1 
LEFT JOIN "Table1" T2 
	ON ( T1."Field1" >= T2."Field1" )
GROUP BY T1."Field1",  T1."Field2",  T1."Field3", ...

Now assume that you have a partitioning on Field3 it will look like

Code: Select all

SELECT COUNT(T2."Field1") AS "RowNum", T1."Field1",  T1."Field2",  T1."Field3", ...
FROM "Table1" T1 
LEFT JOIN "Table1" T2 
	ON ( T1."Field1" >= T2."Field1" ) 
		AND T1."Field3" = T2."Field3"
GROUP BY T1."Field1",  T1."Field2",  T1."Field3", ...

It gets a little bit more complicated when you want to partition on a function like the first three left characters: e.g. LEFT("Field3",3):

Code: Select all

SELECT COUNT(T2."Field1") AS "RowNum", T1."Field1",  T1."Field2",  T1."Field3", ...
FROM (select LEFT(A."Field3",3) as "Part", A.* from "Table1" A )  T1 
LEFT JOIN (select LEFT(A."Field3",3) as "Part", A.* from "Table1" A ) T2 
	ON ( T1."Field1" >= T2."Field1" ) 
		AND T1."Part" = T2."Part"
GROUP BY T1."Field1",  T1."Field2",  T1."Field3", ...

Notes:
Look to the differences between the queries and the repetitive parts (e.g. in SELECT and GROUP BY).
But when Field1 can also be NULL you have to write ON ( T1."Field1" >= T2."Field1 or T2."Field1" IS NULL )
When you ordering is on two fields { Field1, Field2 } you need to have something like { T1.Field1, T1.Field2 } >= { T2.Field1, T2.Field2 } you can write this as ON ( T1."Field1" = T2."Field1" and T1."Field2" >= T2."Field2" or T1."Field1" > T2."Field1" )