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

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