Hello pizzipie
i use OpenOffice on a Windows machine & do not have access to LibreOffice.
using the code below (your original query) saved with 'Run SQL command directly' selected, everything works as expected.
Code: Select all
select "playdate", "score", "player", "id" from "bdata" where "playdate" = '2015-12-07' order by "playdate", "score" desc limit 6
if the 'Sorting and Grouping' window in report builder contains one or more fields then 'Limit' will always fail (all records are displayed).
your report is a flat report & does not require sorting/grouping so hit ('Ctrl+G') & delete any entries.
if the issue persists then you could try running your query as an SQL command.
copy your query.
open report in edit mode.
click anywhere in the grey area.
if necessary hit (F4) to show the properties pane.
hit ('Data') tab.
'Content type' = 'SQL command'.
'Content' = paste your code here & hit ('ENTER')
'Analyse SQL command' = 'No' (when this option is set to 'No' you can hit the '....' gadget to the right of 'Content' & edit your data in sql view).
execute your report with fingers crossed.
Using the 'Rank' query posted by MTP may not be a viable solution because the value stored in the field 'score' may not be unique.
For example below we have 5 scores. 3 are unique & are ranked uniquely. 2 are identical & are ranked identically. if our query contained the code 'where rank <=4' then only 3 rows would be displayed when we wanted 4.
score = 100 rank = 5
score = 100 rank = 5
score = 200 rank = 3
score = 300 rank = 2
score = 400 rank = 1
Edit: just downloaded Villeroy's database, added his query & used it as the source to create a report with Report Builder (literally took me 5 minutes).
before saving the query make sure to select menu:Edit>Run SQL command directly
the query contains 676 rows.
'limit 6' was appended to the query & only the first six rows were shown.
'order by "Category" limit 6' was appended to the query & only the first six rows of the sorted data were shown in ascending order by "Category".
in both cases the result was exactly as asked for.
if you try this & meet with failure then it's likely the problem lies with LibreOffice. |
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10