Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Support of Window Functions in MariaDB

sderom's profile image sderom posted 8 months ago in General Permalink

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?

ansgar's profile image ansgar posted 8 months ago Permalink

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

Description

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

ansgar's profile image ansgar posted 8 months ago Permalink

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

Description

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
sderom's profile image sderom posted 8 months ago Permalink

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 !

ansgar's profile image ansgar posted 8 months ago Permalink

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

Description

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

jery777's profile image jery777 posted 6 months ago Permalink

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

ansgar's profile image ansgar posted 6 months ago Permalink

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

So what happens if you do that?

jery777's profile image jery777 posted 6 months ago Permalink

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.

Description

ansgar's profile image ansgar posted 6 months ago Permalink

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.

jery777's profile image jery777 posted 6 months ago Permalink

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

jery777's profile image jery777 posted 6 months ago Permalink

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.

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.