FIRST (window)

    Syntax

    FIRST( value )
    
    FIRST( value
           [ TOTAL | WITHIN ... | AMONG ... ]
           [ ORDER BY ... ]
           [ BEFORE FILTER BY ... ]
         )
    

    More info:

    Description

    Warning

    The sorting order is based on the fields listed in the sorting section of the chart and in the ORDER BY clause. First, ORDER BY fields are used, and then they are complemented by the fields from the chart.

    Returns the value of value from the first row in the window. See also LAST.

    Argument types:

    • valueAny

    Return type: Same type as (value)

    Examples

    Example with grouping

    Source data

    Date City Category Orders Profit
    '2019-03-01' 'London' 'Office Supplies' 8 120.80
    '2019-03-04' 'London' 'Office Supplies' 2 100.00
    '2019-03-05' 'London' 'Furniture' 1 750.00
    '2019-03-02' 'Moscow' 'Furniture' 2 1250.50
    '2019-03-03' 'Moscow' 'Office Supplies' 4 85.00
    '2019-03-01' 'San Francisco' 'Office Supplies' 23 723.00
    '2019-03-01' 'San Francisco' 'Furniture' 1 1000.00
    '2019-03-03' 'San Francisco' 'Furniture' 4 4000.00
    '2019-03-02' 'Detroit' 'Furniture' 5 3700.00
    '2019-03-04' 'Detroit' 'Office Supplies' 25 1200.00
    '2019-03-04' 'Detroit' 'Furniture' 2 3500.00

    Grouped by [City], [Category].

    Sorted by [City], [Category].

    Result

    [City] [Category] SUM([Orders]) FIRST(SUM([Orders]) TOTAL) FIRST(SUM([Orders]) WITHIN [City]) FIRST(SUM([Orders]) AMONG [City])
    'Detroit' 'Furniture' 7 7 7 7
    'Detroit' 'Office Supplies' 25 7 7 23
    'London' 'Furniture' 1 7 1 7
    'London' 'Office Supplies' 10 7 1 23
    'Moscow' 'Furniture' 2 7 2 7
    'Moscow' 'Office Supplies' 4 7 2 23
    'San Francisco' 'Furniture' 5 7 5 7
    'San Francisco' 'Office Supplies' 23 7 5 23
    Example with ORDER BY

    Source data

    Date City Category Orders Profit
    '2019-03-01' 'London' 'Office Supplies' 8 120.80
    '2019-03-04' 'London' 'Office Supplies' 2 100.00
    '2019-03-05' 'London' 'Furniture' 1 750.00
    '2019-03-02' 'Moscow' 'Furniture' 2 1250.50
    '2019-03-03' 'Moscow' 'Office Supplies' 4 85.00
    '2019-03-01' 'San Francisco' 'Office Supplies' 23 723.00
    '2019-03-01' 'San Francisco' 'Furniture' 1 1000.00
    '2019-03-03' 'San Francisco' 'Furniture' 4 4000.00
    '2019-03-02' 'Detroit' 'Furniture' 5 3700.00
    '2019-03-04' 'Detroit' 'Office Supplies' 25 1200.00
    '2019-03-04' 'Detroit' 'Furniture' 2 3500.00

    Grouped by [City].

    Sorted by [City].

    Result

    [City] SUM([Orders]) FIRST(SUM([Orders]) ORDER BY [City] DESC) FIRST(SUM([Orders]) ORDER BY [Order Sum])
    'Detroit' 32 28 6
    'London' 11 28 6
    'Moscow' 6 28 6
    'San Francisco' 28 28 6

    Data source support

    ClickHouse 19.13, Microsoft SQL Server 2017 (14.0), MySQL 5.6, PostgreSQL 9.3.