Using OVER with an Aggregate Function

  • Comments posted to this topic are about the item Using OVER with an Aggregate Function

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • Hi:

    If select distinct specified order by item must appear in the select list and there will be error message as follows:

    Msg 145, Line 15 St 1

  • Nice question, thank you.

    It works for me.

    Iulian

  • Good question learned something new today.

  • mislamuddin (11/3/2010)


    If select distinct specified order by item must appear in the select list

    It does. The code window has a scroll bar at the bottom; you'll find the column sstr.StateProvinceID to the far right.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Imran Ashraf-452633 (11/4/2010)


    Good question learned something new today.

    (Not targeting you specifically, but everyone who posted and/or will post similar comments)

    The question is good indeed - but please don't learn too much from this.

    If you take away the knowledge that you can use the OVER() clause for all aggregates except CHECKSUM, that's fine. But if you intend to use this query for similar purposes, think again. There are far better ways to get the maximum tax rate per state/province id:

    SELECT MAX(TaxRate), StateProvinceID

    FROM Sales.SalesTaxRate

    GROUP BY StateProvinceID

    ORDER BY StateProvinceID;

    Much easier to understand and maintain in the future, and a more efficient execution plan to boot!

    (EDIT: Fixed a silly typo - thanks, Oleg, for the heads-up!)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • I knew what the answer was, and thought that "The maximum Tax Rates across all State/Province IDs" described it well. I supopose I should have read more closely and realised that "The maximum Tax Rates per State/Province ID" also described it well, then tried to work out the difference between the two statements. Though I'd probably have picked the wrong one anyway.

    Should it have said "The maximum Tax Rate [singular] across all State/Province IDs"?

  • Goo question, thanks, but I don't understand why this was a two point question...

  • I have never used the OVER function before. I had to do a bit of reading to understand the question. I learned about a useful, but limited, function. Good question, thanks.

  • The existence of the Distinct in the query confused me, at first.

    The key to using the Over as opposed to doing a Group By is really when you want to see all the rows in the group, but include an aggregate value.

    With this example, where the only other field being displayed is the StateProvinceID, multiple rows for the same State add little value -- this is likely where the Distinct comes in.

    I have used the Over clause for Row_Number, but not for aggregates, so this question gave me a few things to explore.

  • Hugo Kornelis (11/4/2010)


    (If you take away the knowledge that you can use the OVER() clause for all aggregates except CHECKPOINT, that's fine. But if you intend to use this query for similar purposes, think again. There are far better ways to get the maximum tax rate per state/province id:

    One other important take away, although not illustrated in this question, is that a Select can have multiple Over clauses with different Partitions. This is something that Group By cannot do.

    Select StateProvinceID,

    TaxType,

    Max(TaxRate) Over (Partition by StateProvinceID),

    Max(TaxRate) Over (Partition by StateProvinceID, TaxType)

    from Sales.SalesTaxRate

    Order by StateProvinceID

  • mislamuddin (11/3/2010)


    Hi:

    If select distinct specified order by item must appear in the select list and there will be error message as follows:

    Msg 145, Line 15 St 1

    I did not get this error.

    Of course I also did not get the differance between the first two answers. 😎

    Nice question about a great feature!

  • Very good question - I learned something today.

    Hugo

    If you take away the knowledge that you can use the OVER() clause for all aggregates except CHECKPOINT, that's fine. But if you intend to use this query for similar purposes, think again. There are far better ways to get the maximum tax rate per state/province id:

    To look at the question and only talk about it's limitations makes one wonder why is it there.

    brdudley

    One other important take away, although not illustrated in this question, is that a Select can have multiple Over clauses with different Partitions. This is something that Group By cannot do.

    Excellent addition to the questions functionality. I learned something more, and have already started playing with tests to see about changes, especially in some complex reports.

    Thanks

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • Thanks for the question. I had never considered using the OVER in this way.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I worry that the DISTINCT is going to give people the impression that OVER/PARTITION does the same thing as GROUP BY. It's an important distinction and the explanation doesn't make it clear.

Viewing 15 posts - 1 through 15 (of 33 total)

You must be logged in to reply to this topic. Login to reply