Partager via

GROUP ON ... OVER ... Clause

  • Article

The GROUP ON... OVER ... clause returns a hierarchical rowset which groups search results by the value of one or more columns and ranges you specify. The columns determine the property by which to group, and the ranges determine the buckets into which results are placed. For example, you can group on a date property and specify three buckets of dates (before 2005-1-01, between 2005-1-01 and 2006-6-01, and after 2006-6-01), then nest another grouping by the System.Author property. Furthermore, you can aggregate groupings with AGGREGATE functions.

  • Syntax
  • Nesting Groups
  • Grouping on Vector Properties
  • Labeling Ranges
  • Examples
  • Related Topics

Syntax

The GROUP ON ... OVER clause has the following syntax:

GROUP ON <column> [<grouping ranges>] [ORDER BY ...]
      OVER (...)

The column can be a regular or delimited identifier, limited to the properties in the property store.

The grouping ranges are a list of values to use for grouping the results. If an item doesn't contain the value being grouped by, it is put into a NULL bucket which is always displayed last. For example, a query for communications may return an email draft that was never sent; if the GROUP ON clause specifies the receive date, such a draft message is included the NULL bucket. Within each group, the results are sorted by the column by default.

The following table demonstrates how results are grouped into buckets based the grouping ranges:

Example Result
System.Size [1000, 5000] Results are grouped into four buckets:
  1. Size < 1000
  2. 1000 <= Size < 5000
  3. Size >= 5000
  4. NULL
System.Author [BEFORE('m'),AFTER('r')] Results are grouped into four buckets:
  1. Author < character before "m"
  2. character before "m" <= Author < character after "r"
  3. character after "r" <= Author
  4. NULL
System.Author ["d","m","r"] Results are grouped into five buckets:
  1. Author < "d"
  2. "d" <= Author < "m"
  3. "m" <= Author < "r"
  4. Author >= "r"
  5. NULL
System.DateCreated ['2005-1-01','2006-6-01'] Results are grouped into four buckets:
  1. DateCreated < 2005-1-01
  2. 2005-1-01 <= DateCreated < 2006-6-01
  3. DateCreated >= 2006-6-01
  4. NULL

 

Important  Any ranges you enter must be in ascending order. You cannot, for example, write a GROUP ON clause like this: GROUP ON System.Author["m","z","a"].

 

The optional ORDER BY clause can contain a direction specifier can be either "ASC" for ascending (low to high) or "DESC" for descending (high to low). If you do not provide a direction specifier, ascending is used.

Nesting Groups

You can nest groups with multiple GROUP ON clauses. The order specified in the query is directly reflected in the output group hierarchy, as shown in the following example.

GROUP ON <column1> 
      OVER (GROUP ON <column2> 
                  OVER (SELECT <column3>))
Column1 Column2 Column3
value 1a value 2a value 3a
value 3b
value 2b value 3b
value 3c
value 1b value 2a value 3a
value 2b value 3a
value 2c value 3c
value 2d value 3d

 

Grouping on Vector Properties

Grouping vector properties compares the vectors individually by default. For example, if there is one document, Lorem.docx, with the System.Author property as "Theresa;Paul" and another document, Ipsum.docx, with the System.Author property as "Paul", the query returns two groups:

  1. Theresa
    • Lorem.docx
  2. Paul
    • Lorem.docx
    • Ipsum.docx

As you can see, this method of grouping returns duplicate rows. Lorem.docx appears twice.

 

Labeling Ranges

You can specify a label for each group range for improved readability using the following syntax:

GROUP ON <column> [<grouping range 1>/'<label 1>',<grouping range 2>/'<label 2>']

The label is separated from the grouping range with a backslash and is enclosed in single quotation marks. If you do not specify a label, the group name is the complete range boundary string. If you want to label the minimal bucket, you need to include a minimal value.

The following is an example of using labels for groups:

SELECT System.DateCreated 
FROM SYSTEMINDEX 
GROUP ON System.DateCreated[('2007-1-01')/' "Last Year" ','2007-1-01'/' "This Year" ']

Examples

GROUP ON System.Photo.ISOSpeed [0,10,100] 
      OVER (SELECT System.Title, System.Size, Path FROM systemindex)
            
GROUP ON System.DateCreated['2005/01/01 00:00:00', '2005/12/30 23:00:00'] 
      OVER (SELECT System.Title, System.Size, Path FROM systemindex)
            
GROUP ON System.Author ORDER BY System.author DESC 
      OVER (GROUP ON System.DateCreated ORDER BY System.DateCreated ASC 
                  OVER (SELECT filename, System.DateCreated, System.Size FROM systemindex 
                        WHERE CONTAINS(*, 'text')))

GROUP ON System.Title [before('a'), 'a column', before ('c'), after('d'), 'd column'] 
      OVER (SELECT System.Title, path FROM systemindex ORDER BY System.Title)                        
                        
GROUP ON System.ItemNameDisplay ['a' / 'col_a','c' / 'col_c'] 
      OVER (SELECT System.ItemNameDisplay FROM systemindex 
            ORDER BY System.ItemNameDisplay)

GROUP ON Size[1,2] 
      OVER (GROUP ON System.Author['a','abc','d','x'] 
                  OVER (GROUP ON System.DateCreated['2005/07/25 07:00:00', '2005/08/25 07:00:00']
                        ORDER BY System.DateCreated DESC 
                              OVER (SELECT top 25 FileName FROM systemindex 
                                    WHERE CONTAINS('text'))))