Rank Over Partition does not work

[breirden]

Running Windows 10, fully updated. LO: 6.4.3, Firebird 3 embedded.

I am attempting to use Rank() Over () Partition Order By function in a Query. The data source is also a Query. Neither the partition nor the ranking works. I get no SQL syntax error when executing. Here is the code:

SELECT “Name”, “Date”, “Diff”,

RANK () OVER (
	PARTITION BY "Name"
	ORDER BY "Diff" ASC

) “GRank”

FROM “Query_Games_Hdcp_Diff”

What cannot be seen here is the color scheme I get in the Query SQL editor. The SQL commands are blue, objects are orange (both normal), but the Rank, Over and Partition are green. I cannot find any reference to tell me if the green color is an error, requires some addition type of action or setting, or is not implemented.

The ranking field “GRank” remains at 1 throughout the partition. (Sorry, apparently LO won’t allow me to copy the output of the query so I cannot show the output here). The “Edit>Run SQL Command Directly” option fails with “table unknown”.

According to the Firebird website, the Rank Over Partition capabilities exist and work. My question here: does the LO>Firebird implementation have the same capabilities? Or are these capabilities removed from LO?

Thanks you.

[Ratslinger]

Hello,

Yes, windowing functions in Firebird 3.0 do work in Libre Base. To run in query section you need to turn on Run SQL command directly to bypass the interpreter. This is a toolbar icon on far right or from the menu Edit. Running this based upon another query (depending on its content/construction) may cause problems. Possibly better from a View.

Here is a Rank sample:

and a Rank, Dense Rank sample:

Link to window functions → Window (Analytical) Functions

Edit:

Sorry forgot the important one - Rank with partition: