I can't execute the below nested select :
SELECT * FROM ( SELECT * FROM "lyon" ORDER BY "eclairement_lux" DESC LIMIT 300 ) ORDER BY "eclairement_lux" ASC LIMIT 1
This problem couldn't be solved with the internal HSQLDB. Whith MySQL and a direct connection it works. Direct SQL to the internal HSQLDB doesn't work. In HSQLDB subselects with "ORDER BY" and "LIMIT" won't work. So the bug could only be solved by changing the internal database.
I can confirm, but I would say, that this isn't our bug. But let us see, what other people think about it.
According to bug #31398, I can't insert my temporary results into a table in order to replace this nested select by 2 sucessives queries.
Do you know a workaround in order to implement this query only with SQL ?
(In reply to comment #2)
> Do you know a workaround in order to implement this query only with SQL ?
You could create a view with
SELECT * FROM "lyon" ORDER BY "eclairement_lux" DESC LIMIT 300
Then you could get the right order and the right limit.
Does a view is a query ? If yes, that's what I did. However, querying the view builds a nested "select" : the "select" which is made on the view contains the "subselect" which defined the view.
Created attachment 75743 [details]
Queries in this database solve the problem with the internal HSQLDB in this bug-report.
The problem with the nested select and keywords "ORDER"and "LIMIT" couldn't be solved by LO when using the internal HSQLDB. The database shows a way to get the same result as wished in this bug-report. So I will change this report to "NOTOURBUG". Hope, that this is the right decision for the reporter, too. If not, feel free to reopen this bug.