Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19017

incorrect LAST_VALUE() result

    XMLWordPrintable

Details

    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

      Attachments

        Activity

          People

            varun Varun Gupta (Inactive)
            danblack Daniel Black
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.