CODEWITHSUNDEEP
sql-server
mtmacdonald
mtmacdonald

Reputation: 15070

Get the first value instead of an aggregate value in GROUP BY

I'm using GROUP BY with aggregation functions to aggregate a table (grouping by multiple foreign keys, a value column, and a timestamp column that's rounded down to whole days).

But there is a column where I need to get the first row in the grouping, instead of an aggregate value. I've looked at FIRST_VALUE() but it's a window function, not an aggregation function.

In pseudo-code I'm looking to do this [FIRST() function doesn't exist]:

SELECT
    ForeignKey1
    ,ForeignKey2
    ,MIN(StartTime) AS StartTime
    ,Foo
    ,AVG(Bar) AS Bar
    ,FIRST(Baz) AS Baz
FROM 
    Qux
GROUP BY 
    ForeignKey1
    ,ForeignKey2
    ,Foo
    ,CONVERT(DATE, StartTime)

How do I get the first value instead of an aggregate value?

Upvotes: 2

Views: 542

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

You can apply row numbers in a CTE and then pull out the entire first row, no need for the separate MIN aggregate:

;With Ordered as (
    SELECT
        *,
        ROW_NUMBER() OVER(
           PARTITION BY
             ForeignKey1
             ,ForeignKey2
             ,Foo
             ,CONVERT(DATE, StartTime)
           ORDER BY StartTime) as rn,
        AVG(Bar) OVER (
           PARTITION BY
             ForeignKey1
             ,ForeignKey2
             ,Foo
             ,CONVERT(DATE, StartTime)) as BarAvg
    FROM Qux
)
SELECT * from Ordered
WHERE rn = 1

Upvotes: 2

Related Questions