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

            • Assignee:
              varun Varun Gupta
              Reporter:
              danblack Daniel Black
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: