snippetMinor
How to allow window function in window specification?
Viewed 0 times
specificationfunctionallowhowwindow
Problem
I'm trying to run this query:
This query works fine with MySQL, but on this another db server, on MariaDB (version:
I searched in PhPMyAdmin for an option to allow window function, but I didn't find.
How can I allow it?
SELECT uuid,
brainffa - LAG(brainffa, 1) OVER (ORDER BY brainffa) as brainffa
FROM time_played
WHERE automatic = 1 AND name LIKE 'all%'
ORDER BY uuid DESC LIMIT 15
This query works fine with MySQL, but on this another db server, on MariaDB (version:
10.6.5-MariaDB-1:10.6.5+maria~bionic) it failed with this error:#4016 - Window function is not allowed in window specificationI searched in PhPMyAdmin for an option to allow window function, but I didn't find.
How can I allow it?
Solution
As Mustaccio said, the error comes from alias.
By using
My final SQL query:
By using
as brainffa_result, it works. Now, it's fine with MariaDB and MySQL.My final SQL query:
SELECT uuid,
brainffa - LAG(brainffa, 1) OVER (ORDER BY brainffa) as brainffa_result
FROM time_played
WHERE automatic = 1 AND name LIKE 'all%'
ORDER BY uuid DESC LIMIT 15
Context
StackExchange Database Administrators Q#316276, answer score: 2
Revisions (0)
No revisions yet.