For the original poster ( GauSte ), I wanted to demonstrate for you and example of getting the results I think you desire, from:
- A Select query
- Reading a CSV OR TXT file
This is being accomplished by using
NOT the
Embedded HSQL database ( Version 1.8.1.10 )
BUT rather by using an
H2 database engine.
Please see the link below for downloading the database engine
H2
http://www.h2database.com/html/main.html
An
H2 database can be used by either
OpenOffice or
LibreOffice by including the
H2 JAR file, and, for your
*.odb file including the correct connection criteria.
H2 database (
NOT HSQL ) includes two built-in functions that probably would make the Query you desire very easy. They are:
- CSVREAD see: http://www.h2database.com/html/function ... AD#csvread
- DENSE_RANK see: http://www.h2database.com/html/function ... _aggregate
Below is an example of using
BOTH CSVREAD and
DENSE_RANK for a connection to a flat csv file:
Code: Select all
Select
"MY_ATTENDANCE"."TEST_DATE" as "TEST_DATE",
"MY_ATTENDANCE"."TEST_TIME" as "TEST_TIME",
-- Sample use of DENSE_RANK Function
DENSE_RANK() OVER (ORDER BY "MY_ATTENDANCE"."TEST_DATE" DESC) as "DENSE_RANK"
-- Line Below Reads a .csv file from defined directory with an alias name of "MY_ATTENDANCE"
From CSVREAD('C:\Program Files (x86)\MyDownloads\bldtest9.csv') as "MY_ATTENDANCE"
Where "MY_ATTENDANCE"."TEST_DATE" Between '2019-10-15' and '2020-01-15'
Order By "MY_ATTENDANCE"."TEST_DATE" DESC,
"MY_ATTENDANCE"."TEST_DATE" ASC
Additional note, the OpenOffice / LibreOffice BASE PARSER accepts this as written.
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add
[Solved] in your
1st post Subject (edit button top right) if this issue has been resolved.