Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3.3
-
None
-
Ubuntu 16.04 LTS, Virtual Machine 4 cores / 8GB RAM
4.4.0-108-generic #131-Ubuntu SMP Sun Jan 7 14:34:49 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
Description
https://gist.github.com/paul78oz/4ea9f626ae10333a5abaacf828804f94
CREATE TABLE book_rating (id TINYINT, name CHAR(30), star_rating TINYINT); |
|
INSERT INTO book_rating VALUES (1, 'Lord of the Ladybirds', 5); |
INSERT INTO book_rating VALUES (2, 'Lord of the Ladybirds', 3); |
INSERT INTO book_rating VALUES (3, 'Lady of the Flies', 1); |
INSERT INTO book_rating VALUES (4, 'Lady of the Flies', 2); |
INSERT INTO book_rating VALUES (5, 'Lady of the Flies', 5); |
INSERT INTO book_rating VALUES (6, 'Lady of the Flies', 5); |
INSERT INTO book_rating VALUES (7, 'Lady of the Flies', 5); |
INSERT INTO book_rating VALUES (8, 'Lady of the Flies', 5); |
INSERT INTO book_rating VALUES (9, 'Lady of the Flies', 5); |
INSERT INTO book_rating VALUES (10, 'Lady of the Flies', 5); |
INSERT INTO book_rating VALUES (11, 'Lady of the Flies', 5); |
INSERT INTO book_rating VALUES (12, 'Lady of the Flies', 5); |
INSERT INTO book_rating VALUES (13, 'Lady of the Flies', 5); |
INSERT INTO book_rating VALUES (14, 'Lady of the Flies', 5); |
INSERT INTO book_rating VALUES (15, 'Lady of the Flies', 5); |
|
// This works as expected |
SELECT name, median(star_rating) OVER (PARTITION BY name) FROM book_rating; |
|
// This doesn't work |
SELECT name, median(star_rating) OVER (ORDER by id ROWS 3 preceding) from book_rating |
|
// This works as expected |
SELECT name, avg(star_rating) OVER (ORDER by id ROWS 3 preceding) from book_rating |
As per expanded example from documentation and attached, cannot seem to get working MEDIAN using ORDER BY:
eg. SELECT name, median(star_rating) OVER (ORDER by id ROWS 3 preceding) from book_rating
Results in:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER by id ROWS 3 preceding) from book_rating
No errors showing in mariadb logs.