[Solved] Row_Number()

Creating tables and queries

[Solved] Row_Number()

Postby silvrwood » Mon Jul 02, 2018 5:44 am

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/?
Last edited by robleyd on Tue Jul 03, 2018 1:17 am, edited 2 times in total.
Reason: Add green tick [robleyd, Moderator]
Apache OpenOffice 4.1.5
Win10 Home x64
silvrwood
 
Posts: 14
Joined: Sat May 19, 2018 6:57 pm

Re: Row_Number()

Postby keme » Mon Jul 02, 2018 8:04 am

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.
User avatar
keme
Volunteer
 
Posts: 3157
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Row_Number()

Postby Villeroy » Mon Jul 02, 2018 11:01 am

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.
https://forum.openoffice.org/en/forum/v ... 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().
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26730
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Row_Number()

Postby Sliderule » Mon Jul 02, 2018 3:25 pm

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.
User avatar
Sliderule
Volunteer
 
Posts: 1170
Joined: Thu Nov 29, 2007 9:46 am

Re: Row_Number()

Postby silvrwood » Mon Jul 02, 2018 4:17 pm

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

Thank you to everyone for responding.
Apache OpenOffice 4.1.5
Win10 Home x64
silvrwood
 
Posts: 14
Joined: Sat May 19, 2018 6:57 pm

Re: Row_Number()

Postby eremmel » Mon Jul 02, 2018 4:47 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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" )
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1035
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 5 guests