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

Add support for LIMIT clause in GROUP_CONCAT()

    XMLWordPrintable

Details

    • 10.2.4-4, 10.1.20, 10.2.4-5, 10.2.4-1, 10.2.4-2, 10.3.3-1

    Description

      GROUP_CONCAT supports ORDER BY and DISTINCT but not LIMIT clause.
      Supporting LIMIT N[,M] could simplify queries.

      something like

      SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",date,cnt) ORDER BY cnt DESC),",",1)

      could be written as

      GROUP_CONCAT(CONCAT_WS(":",date,cnt) ORDER BY cnt DESC LIMIT 1)

      this can be used in timeseries-like calculations. Suppose we have a table (date,cnt) and we want to produce a list of dates, for each year, where cnt was maximum for that year, showing such date,cnt for each year

      currently this is written in an awkward manner and is also quite inefficient as we SELECT much more data that is necessary for the task:

      SELECT LEFT(col1,4) AS _yyyy
      , SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",DATE_FORMAT(col1,"%a %Y-%m-%d"),LPAD(col2,8," ")) ORDER BY col2 DESC),",",1) AS col3
      FROM t1
      GROUP BY 1
      ;

      Attachments

        Issue Links

          Activity

            People

              varun Varun Gupta (Inactive)
              varun Varun Gupta (Inactive)
              Votes:
              2 Vote for this issue
              Watchers:
              8 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.