Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3.11, 10.2.19, 10.2, 10.3, 10.4
-
None
Description
https://dba.stackexchange.com/questions/232814/last-value-in-two-levels-of-group-by-in-mysql
https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=803339c7ce6ae11967944c427b01ff8b
CREATE TABLE fruits_flow (
|
`date` date,
|
`fruit` VARCHAR(7),
|
`init` INTEGER,
|
`end` INTEGER
|
);
|
|
INSERT INTO fruits_flow
|
(`date`, `fruit`, `init`, `end`)
|
VALUES
|
('2018-01-02', 'Oranges', '1479', '1480'),
|
('2018-01-03', 'Oranges', '1478', '1504'),
|
('2018-01-04', 'Oranges', '1507', '1494'),
|
('2018-01-05', 'Oranges', '1510', '1551'),
|
('2018-01-02', 'Apples', '2150', '2196'),
|
('2018-01-03', 'Apples', '2161', '2167'),
|
('2018-01-04', 'Apples', '2192', '2169'),
|
('2018-01-05', 'Apples', '2169', '2194');
|
|
SELECT
|
CONCAT(YEAR(`date`),'/',WEEK(`date`)) as `week`,
|
fruit,
|
FIRST_VALUE(init) OVER (PARTITION BY fruit,`week` ORDER BY `date` ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 'first',
|
LAST_VALUE(end) OVER (PARTITION BY fruit,`week` ORDER BY `date` ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 'last'
|
FROM fruits_flow
|
GROUP BY `week`,fruit
|
Results:
week | fruit | first | last |
:----- | :------ | ----: | ---: |
2018/0 | Apples | 2150 | 2196 |
2018/0 | Oranges | 1479 | 1480 |
Expected Results
week | fruit | first | last |
:----- | :------ | ----: | ---: |
2018/0 | Apples | 2150 | 2194 |
2018/0 | Oranges | 1479 | 1551 |