How to find LAST record in each Group, in a Table, using a Query?

English
#1

I’ve spent several days working on this without success!

I have a table call “MARKET_PRICE”.
The fields in this table are “PriceKEY”, “StockAbbrev”, “QuoteDate”, and “Price”. PriceKey is automatically generated.

For each StockAbbrev, there are several records with various QuoteDates and associated Prices. (stockmarket quotations over several days).

I’m trying to extract the latest QuoteDate (& Price) for each StockAbbrev, using a Query.

I sought help on ChatGPT and GoPilot (AI’s) who made several suggestions, but I couldn’t get any to work. Perhaps some limits with Base’s SQL??

There should be an easy solution, as I expect this is a very common requirement by users (getting FIRST or LAST record in a set).

I appreciate any help (I’m new to Base and SQL). SQL is so unforgiving, and the error messages are totally useless (“Syntax error” doesn’t help much when it doesn’t tell you where that error is!).

I’m using LibreOffice 24.8.2.1 on an iMac with MacOS 13.7 (Ventura).

#2

As you don’t tell what you tried this is hard to find out. But actually Base has no own SQL. Base can connect to several datasources and allow you sql-queries. If your database is of types text or dBase, features are very limited.
.
LibreOffice brings two (embedded) databases HSQLDB and Firebird, with slightly different SQL, but you may also connect to Postgres, MySQL/MariaDB.
.
So your solution needs to fit your used database.

Not everything common is easy, but you may start reading at the following link. (No AI, just another website, accessible for google. Found with “sql query last record in group by latest” - the second half suggested by google itself.)

.

Think again. SQL is totally forgiving. You are always allowed a next try. “It” will never complain, even if you try the same again…

#3

https://ask.libreoffice.org/uploads/short-url/lavNozIoLu1ZbFJvOSvzZGqu2wl.odb

#4

@FrankF,
the appropriate sql will depend on the engine you use.
.
if you use the default hsqldb embedded database then the code below should suffice, I have quoted all alias’s so it should run indirectly i.e. will not insist on direct sql .
.
first you have to get max(“QuoteDate”) for each “StockAbbrev”
then
use a sub-query to get the “Price”.
.
if you use Firebird then it’s quite different.
.
EDIT: had second and third thoughts about this and have changed the sql because you could have multiple records for the same “StockAbbrev” on the same date at different prices therefore I am showing the max(“Price”) for transactions made on max(“QuoteDate”).
there is no 100% reliable formula to retrieve the last record given your dataset

select "m"."StockAbbrev", "m"."QuoteDate", "m"."mx_price" "LatestPrice"
from
(
	select
		"StockAbbrev", max("QuoteDate") "mx_dte"
	from
		"MARKET_PRICE"
	group by
		"StockAbbrev"
) "mx"
join
(
	select
		"StockAbbrev", "QuoteDate", max("Price") "mx_price"
	from
		"MARKET_PRICE"
	group by
		"StockAbbrev", "QuoteDate"
) "m"
on "mx"."StockAbbrev" = "m"."StockAbbrev" and "mx"."mx_dte" = "m"."QuoteDate"
#5

Thanks! It worked! But I now have 2 more questions:
a) In this Market_Price table there can be many records for each StockAbbrev, containing various QuoteDates with Prices. At the moment a StockAbbrev can have multiple records with the same QuoteDate. How can I ensure that for each StockAbbrev, each QuoteDate must be unique. Is there a way to construct a Key field by concatenating StockAbbrev with QuoteDate?

b)The Query you gave me creates a response that looks like a table with three columns: StockAbbrev, LatestQuoteDate, LatestPrice. I’d like to pass those dates and prices to another table who’s Key=StockAbbrev. How best to accomplish this?

#6

The question is: What is your goal?
.
To combine two tables use JOIN.
If you wish to update existing values in the second table, there is “UPDATE table SET …”
If you wish to append the values to another table “SELECT … INTO table”

#7

https://forum.openoffice.org/en/forum/viewtopic.php?t=56006

#8

@FrankF,
when creating a database it’s vital to spend time pondering your objectives and assessing how best those objectives may be achieved.
even a simple db requires solid foundations, if the structure is not capable of delivering the desired outcome then your project will end in failure.
.
a) it would be time consuming for me to try and explain what I do and why I do it, please take a look at the attachment it shows how you may achieve your objectives.
.
b) duplicating table data violates the most basic rule of normalisation and is almost always avoidable, sql queries are the preferred alternative solution.
Last_Price.odb (15.0 KB)

#9

I’m creating a DB to keep records of investments. My tables are:
INVESTMENT: to name and describe the investment; its key is “StockAbbrev”.
TRANSACTION: for all transactions relating to the investments (like Buy, Sell, Dividend/Interest earned (both re-invested & cash varieties). Its key is an automated integer.
MARKET_PRICE: to store the market price/unit (share) on various dates. Its key is an automated integer. But I’d like to be able to ensure that there cannot be two or more quotes on the same date for any investment (not sure how to accomplish this). I’d need a key that’s like a concatenation of “StockAbbev”||“QuoteDate”, or something like first and second keys.

Three forms, so far, for manual data entry to each of these tables. These are working OK.

Aim is to eventually produce reports showing the overall portfolio, listing the investments, when they started, how much was put in (or taken out), how much each earned, and “current” worth based on latest QuoteDates in the MARKET_PRICE table.

Most calculations are based on figures in the transaction records. There are some cross-calculations (using figures in a transaction record and storing the result in another field of the same record.

I have a query that summarizes calculated figures for each investment. I’d like to store some of the summarized figures in the ‘upper’ table (i.e. INVESTMENT) so they’re visible when looking at the form for investment data entry. I hope these figures are refreshed (recalculated) every time I open the database! …or perhaps manually triggered to refresh while in the database.

Hope this explains why I want to be able to see the latest price and its quote-date for each investment (to calculate ‘current’ worth = total units X latest_price), and to pass this information to the investment’s record in the INVESTMENT table.

I will also want to get the date of the first transaction for each investment, and pass that to the invement’s record in INVESTMENT table. This indicates when the investment began.

I’m finding that when I have a calculated field in row, and give it an alias name, I can’t use that alias name in other calculations. I have to repeat the calculation’s SQL coding (which can be lengthy) in any other column where I need this result for the further calculation. Not sure of the purpose of alias names (though useful when the normal name cell is filled with SQL coding).

I worked (now retired) in IT for many years as a programmer (Autocoder, COBOL, bit of FORTRAN, SAS etc) and had a bit of experience with Microsoft Access. More recently, I’ve been using Apple’s FileMaker quite a bit which works fine but has a different approach than Access or Base.

I’ve used OpenOffice Spreadsheet for several years, but now I’d like to get familiar with Base and am using this investment thing as my first project (I developed a similar one in FileMaker, so my table design is similar to that). I was told that LibreOffice is perhaps better than OpenOffice, so here I am!

I’m not experienced in coding SQL, and haven’t found a good reference guide which spells out the syntax, and gives useful tips for various situations. I’ve been using ChatGPT quite a bit to look at my SQL coding to tell me what’s wrong with it (Base’s diagnostic messages are useless!). The AI is great at pointing out syntax errors and making suggestions with revised SQL coding. But their suggestions don’t always work in Base (“Syntax error” again!), so there’s a LOT of ‘trial & error’ attempts to get it working. It’s been taking up a LOT of time, and can be very frustrating.

So, I very much appreciate all the help I can get with this. THANKS to everyone who has pitched in with suggestions!!

There’s more yet to do with this project. I haven’t even begun on Reports. I want to make sure the underlying data is working as it should. I’ve got a ways to go with that!

#10

Usually you would not repeat data in a concatenated field, butt it would be possible by ussing TRIGGER.
.
Better use compound keys or an UNIQUE INDEX wich reference the fields/columns.
The following is an example for SQLITE for a primary key of more than one column. I usedbthis for example to import archives wich had “numbers” 1,2,2a,3, 4a,4b in 2 columns

CREATE TABLE something (
  column1 INTEGER NOT NULL,
  column2 INTEGER NOT NULL,
  value,
  PRIMARY KEY ( column1, column2)
);

But you may also use an index, wich may be easier to do, if you already have created your database.
See the discussion here:

#11

As “Base” is not the database, but a connecting Module of LibreOffice you have to search this at the actual database you use. Sadly SQL varies a lot…
.
For the older 1.8 version of HSQLDB in use as embedded database in LibreOffice you could read this
https://www.hsqldb.org/doc/1.8/guide/
.
Please note: If your AI is not trained for some special version of SQL, it is most likely to fail in details. Remember always: it is not really understanding your code, but giving you a “most likely” answer. To improve this you should use the “most likely” database :grin:
.
I have seen fine looking generated code, wich did not work, because some functions used were simply “invented” and not available in real world. Syntax will not change because ChatGPT or me writes “create compound key of x,y” in uppercase letters

#12

How to connect to Postgres or MySQL?____
Are they considered better (or easier to use) than HSQLDB?___

When changing engines, is it necessary to make changes to the current database (tables, queries, forms etc)?____

I get MANY ‘Syntax error’ messages with every query I write. Still happens when I try a “corrected” query suggested by ChatGPT. ‘Syntax error’ is a useless diagnostic message …it doesn’t specify what (or where) the error is. I have spent days (literally) trying to achieve what I think should be easy queries, but am not making much progress.

I’m also finding that if I have a Query that does some calculations, it’s impossible to use those results in other calculations or other Queries. The ‘syntax error’ message displays a long page of SQL coding (that was behind the original calculations). I have yet to succeed in combining calculated results in two separate queries to do another calculation. Nothing has worked so far, after many frustrating hours of attempts. I’ve also never succeeded in UPDATE…ing an existing Table with any field values from a Query. Another ‘syntax’ error, or totally blank window. Just doesn’t work.

I haven’t even started on reports yet. Every time I click Reports, LibreOffice (v24.8.2.1) crashes.

If I don’t have more success soon, I’m going to give up on this project and stay with Apple FileMaker Pro (which I’m more familiar with).

#13

@FrankF

no it did not work, I was expecting a considered analytical reply.
not only do you require the last record as input you also need to retain the sequence of the records as input.
we could add a field to our table and manually input the the value of each records position within its group but that would be both impracticable and fail to prevent “Date” typos.
.
I believe you have something like this:
1, ‘2024-01-01’, 100.50
2, ‘2024-01-01’, 110.50
3, ‘2024-01-01’, 105.50
4, ‘2024-01-01’, 103.50
.
record number 4 is the last record entered.
max(ID) will return 4
BUT
1, we are insisting that the user inserts each record in the correct sequence.
2, if the user miss-types a date then that record will end up in the wrong group.
HENCE
“Last_Price.odb”.
input the date once and use a second table (auto value primary key, “Price”, FK “tMarketPrice”.“ID”) to input the prices, the order of input is the value of the auto value primary key.
the sequence is always retained and is reflected in the form (just sort by ID).
.
hsqldb embedded is very old, it does not have the latest features/functions.
embedded db’s are useful for uploading examples/demos but totally unsuitable for serious projects.
in my opinion the best open source db is PostgreSQL closely followed by MariaDB.
the flavour of sql does vary between dbs but is broadly similar.
the project you outline will require familiarity with your chosen tool, knowledge of sql and the ability to design libreoffice forms.
.
changing engines is always painful.
.
syntax errors? read the manual Hsqldb User Guide

#14

The second sample in topic Apache OpenOffice Community Forum - [Example] Invoice Forms (without macros) - (View topic) demonstrates how to copy article prices at invoice dates by means of subforms. A subform is filtered by specific value(s) in its parent form’s selected records. When creating a new record, the subform inherits the specific value(s) from its parent form.

My database link https://ask.libreoffice.org/uploads/short-url/lavNozIoLu1ZbFJvOSvzZGqu2wl.odb demonstrates how to look up article prices at invoice dates. Form “Existing Invoices” shows the invoices in the left yellow grid and the sold items with quantities of table “Sales” in the right yellow grid. The prices of sold items at invoice time are shown in a white grid.
The report shows all invoices with the sold items, their prices at invoice time, quantities, prices*quantity and the total calculated by the report engine. The report source is a query “Invoices_Report” which is based on the tables and another query named “Query1”. The latter does the lookup job by looking up the latest date being smaller than or equal to the invoice date for each combination of invoice and article.
Query1:

SELECT "INV"."ID" AS "INVID", "PRICES"."PID", MAX( "PRICES"."D" ) AS "MD" 
FROM "PRICES" JOIN "PROD" ON "PROD"."ID" = "PRICES"."PID" JOIN "INV" ON "PRICES"."D" <= "INV"."D" 
WHERE "EX" = FALSE 
GROUP BY "PID", "INV"."ID"

side note: WHERE "EX" = FALSE should not be in that query. The “EX” field is meant to mark expired articles, so they are not selectable for new invoices. However, that query selects all invoices of the past. Since no article in that demo is marked as expired, my mistake has no effect.

Both samples should work with any type of connection to a true database engine. The first one with the copied values might be faster with large amounts of articles and prices. And the remarks on Base are worth reading.
The second sample made by myself may be more puristic. Database purists don’t copy values that are already stored in the database.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.