Support of Window Functions in MariaDB

Window functions were introduced in MariaDB 10.2.0. Does HeidiSQL support window functions such as ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, COUNT, SUM, AVG, BIT_OR, BIT_AND and BIT_XOR?

Some of these are listed in the context sub menu "Insert function":

In what way would you expect support for these functions, apart from the fact that you can use them in your queries?

By the way, these listings just got a tooltip with their help text in the next build:

So when I don't find the function (like ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE), it means HeidiSQL doesn't support it?

PS: the help text is really great !

What do you mean with "I don't find the function"? I suppose you just can't find it in the SQL proposal:

But that just means that function list should get another update. You can still fire such queries.

So specifically the ROW_NUMBER() function doesn't appear to work.

SELECT ROW_NUMBER() OVER (PARTITION BY type) AS 'rowNum', itemNum, type FROM items

The goal of this would be to return a row number for the itemNum for each type. ex.

rowNum itemNum type 1 111 chair 2 112 chair 3 113 chair 1 114 couch 2 115 couch

So specifically the ROW_NUMBER() function doesn't appear to work

So what happens if you do that?

I just get a syntax error as if the function doesn't exist. It also doesn't show up anywhere in the list of functions like sderom was saying.

But that is a server-side error message - HeidiSQL just passes these through to you.

Additionally, that ROW_NUMBER() OVER... syntax is SQL Server specific. At least sderom from above said he was working on MariaDB.

That's fair. I guess then I'm not sure how to find a MariaDB version of the row number function.

for those that are still looking this will accomplish what I was trying.

SELECT ranked.rank, ranked.tkey, ranked.ttype FROM ( SELECT tkey, ttype, @test_rank := IF(@current_rank = ttype,@test_rank + 1,1) AS rank, @current_rank := ttype FROM test ORDER BY ttype DESC) ranked

That gives the same results as what ROW_NUMBER with PARTITION BY would give.