HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

How to allow window function in window specification?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
specificationfunctionallowhowwindow

Problem

I'm trying to run this query:
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 specification


I 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 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.