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?
Support of Window Functions in MariaDB
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
But that is a server-side error message - HeidiSQL just passes these through to you.
ROW_NUMBER() OVER... syntax is SQL Server specific. At least sderom from above said he was working on MariaDB.
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.