[Solved] Add a numbered field

[GauSte]

Hi,
I have imported a CSV table into Base, ordered by date, and I would like to create a query to first order by 'name' field, then to add a numbered field, incrementing when 'name' field value is changing :

From :

Code: Select all

| name  | date_absence|
| Larry | 05/09/2020  |
| Sarah | 07/09/2020  |
| Sarah | 12/09/2020  |
| Sarah | 18/09/2020  |
| Henri | 21/09/2020  |
| Henri | 25/09/2020  |
| Anna  | 25/09/2020  |
| Anna  | 27/09/2020  |
| Sarah | 01/10/2020  |
| Anna  | 05/10/2020  |

I would like to get this :

Code: Select all

| name  | date_absence| rank |
| Anna  | 25/09/2020  | 1   |
| Anna  | 27/09/2020  | 1   |
| Anna  | 05/10/2020  | 1   |
| Henri | 14/09/2020  | 2   |
| Henri | 25/09/2020  | 2   |
| Larry | 05/09/2020  | 3   |
| Sarah | 07/09/2020  | 4   |
| Sarah | 12/09/2020  | 4   |
| Sarah | 18/09/2020  | 4   |
| Sarah | 01/10/2020  | 4   | 

Working on HSQLDB 1.8.

Thanks

[eremmel]

Here you go:

Code: Select all

select B."name" , A."date_absence",  B."rank"
from  "absences" A
inner join 
(  select "name", count(*) as "rank"
   from (select distinct "name" from "absences") X
   inner join (select distinct "name" from "absences") Y on X."name" >= Y."name"
   group by "name" 
) B on A."name" = B."name"

[GauSte]

Thanks for your help.
Unfortunateley, it seems not to work ('name' and 'rank' fields stay empty), because the base is a text base from CSV file and not a really HSQL base.
So 'Group By' doesn't work.
It would be nice with a really HSQL base, but I need to get data from a CSV file.

[Villeroy]

HSQL can handle text files: [Tutorial] Using csv/text files as editable data source.

[Villeroy]

This turns out to be almost the same topic as [Solved] Repeat latest group 1 for each group But what is the purpose of ranking by alphabetical value? Are you trying to get a unique index or something?

[Sliderule]

For the original poster ( GauSte ), I wanted to demonstrate for you and example of getting the results I think you desire, from:

1. A Select query
2. 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:

1. CSVREAD see: http://www.h2database.com/html/function ... AD#csvread
2. 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.

[GauSte]

Hi,
Thanks to Villeroy with

HSQL can handle text files: [Tutorial] Using csv/text files as editable data source.

The solution is not to execute queries in Base on CSV text tables, but to import the CSV file into a predefined HSQL table with

Code: Select all

SET TABLE "Names" SOURCE "names.csv"

And then the Group by queries work.

Thanks also to Sliderule for the interesting solution, but I would not like to install a new DB engine.

[Villeroy]

Thank you for the feedback. Yes, it is not trivial but easy enough to tell HSQL about an existing csv file and treat it like a table. This solution may show heavy performance problems and you will get wrong data types when dates, times or comma-decimals are involved. The later problems can be solved with the help of a view which converts text to appropriate field types. Then the performance problems can be solved by a simple copy/paste into a binary table.
For instance the following expression converts a US-date ("12/31/1999") into a true date value. It concatenates the relevant substrings to "1999-12-31" and the CAST function is able to convert this ISO date:

Code: Select all

SELECT CAST(SUBSTR("Birthday", 7, 4) ||'-'|| SUBSTR("Birthday", 1, 2) ||'-'|| SUBSTR("Birthday", 4, 2) AS DATE) AS "DateValue" FROM "CSV_Table"

@Sliderule
Thank you for the head-up. H2 is definitively a very good alternative to HSQL. Both engines comply to the same standards that use to work well with the Base frontend. H2 seems to have a lot more extras, though.