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

support from last / first first with nth_value window function

    XMLWordPrintable

    Details

      Description

      The nth_value function can support some additional syntax "from last" and "from first". This is supported by columnstore and most other databases with window functions support:

      NTH_VALUE(column, n) selects the value of the column for the nth row in the window. FROM FIRST says select the nth from the beginning of the window. FROM LAST says select the nth counting backwards from the end of the WINDOW. The functions FIRST_VALUE and LAST_VALUE are specializations of each of these with n=1.
      For the examples, four and ten are integer columns of tenk1.
      Here's a statement without FROM LAST (the default is FROM FIRST)
      SELECT NTH_VALUE(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1;
      Here it is with FROM LAST
      SELECT NTH_VALUE(four, 0) FROM LAST OVER (ORDER BY ten), ten, four FROM tenk1;
      RESPECT NULLS says to select NULL as the value if it happens to be in the nth row. IGNORE NULLS says to not count rows that are NULL. The default is RESPECT NULLS.
      For the following sequence:
      1
      NULL
      3
      As far as NTH_VALUE is concerned, if IGNORE NULLS, then there are only two rows in the window.
      SELECT NTH_VALUE(four, 0) FROM LAST IGNORE NULLS OVER (ORDER BY ten), ten, four FROM tenk1;

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              dthompson David Thompson (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration