Ask Your Question

LIBRE BASE Query won't run

asked 2020-02-27 02:48:49 +0200

JohnJPDX gravatar image

I have 20 lines of data in a table. I created a query sorting data by date and limited it to only the 10 newest lines. That query works fine. When I try to create a query taking one column from the first query and averaging it the query fails.

Error message starts with: Cannot be in ORDER BY clause in statement [SELECT AVG( "Differential" ) AS "Differential" FROM ( SELECT "Differential" AS "Differential", "Player" AS "Player", "Date"

Don't understand why it can't run a query off another query with a SORT in it. I can't even just create a duplicate query of the first query.

Just updated to running HSQLDB.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-02-27 05:43:34 +0200

Ratslinger gravatar image


Difficult to tell your problem without the complete SQL from each.

This seems to be a case of the HSQLDB version. Appears you are using a sub query which works with newer versions but not with the one with the one included with Base. It is very old - v1.8

For this version it may be best to create a View for the sub query and use that as the source. In the View, include the sorting but use the Limit aspect in the Query. This seems to work for this version.

edit flag offensive delete link more


As with all questions, if this answers your question please tick the ✔ (upper left area of answer). It helps others to know there was an accepted answer.

Ratslinger gravatar imageRatslinger ( 2020-02-27 05:45:34 +0200 )edit

Ok, how can I get the newer version of the DB?

JohnJPDX gravatar imageJohnJPDX ( 2020-02-27 07:25:22 +0200 )edit


The method to use a newer version of HSQLDB is to use a split database. The process can be found here -> [Wizard] Create a new 'split' HSQL 2.x database. This also allows you to change to a different version easily.

However, LO has also included the Firebird embedded database as its' replacement of HSQLDB v1.8 embedded (no time frame set). Although there are still modifications being done, most problems have workarounds (many posted on this site). Have had this running and answering questions here for over 18 months now (search Firebird). There are also many other databases you can use such as MySQL, MariaDB, PosgreSQL and others.

The choice is yours and you should always investigate the capabilities of a database (all have manuals available with an internet search) before settling on one.

Ratslinger gravatar imageRatslinger ( 2020-02-27 18:06:25 +0200 )edit

Thanks for all the help. I ended up going back thru all the parent queries and just sorting them by date from the tool bar rather than a SORT statement. Somehow this worked and the data all came down in date order and I could use the limit to pull the desired data. I don't know how this will work when I add new data. Hopefully the date sort will remain in place.

JohnJPDX gravatar imageJohnJPDX ( 2020-03-03 08:41:58 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-02-27 02:48:49 +0200

Seen: 39 times

Last updated: Feb 27