Re: Aggregate function question....

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: JSweetser@xxxxxxxx
  • Date: Fri, 27 May 2011 13:59:52 +0200

Joe,

I assume that your colleague wants to display BOTH the first value and the current value, otherwise s/he could easily wrap the query in an inline view and select rows for which the rank is equal to one.

IMHO the best option is probably to use a recursive WITH statement, and return the previous value unless you change group but there are fewer options for ordering with SQL Server than with 11gR2, it may be rather difficult to put in place and performance-wise I am not sure that it would be efficient. I understand the reluctance to have another query bearing on the same data, I would feel the same, but it may well be the best available option. However, since there is an aggregate in the over () clause, I assume that the full query is rather complex and using query factoring (i.e. WITH) may soften the blow.

HTH
--
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>


On 05/25/2011 09:57 PM, Sweetser, Joe wrote:

....for SQL server 2005.

I got this from a developer and I don't know too much about aggregate functions period and certainly not on sql server.

Can anyone suggest an alternative? I thought min/max might work but those do not depend on the order of the data while first function in oracle does (intentionally, of course). Duh.

Thanks,

-joe

<snip>

I am trying to perform an aggregate function that I partition by two keys, order by descending coverage amount and then take the first value. In oracle I would use:

, first(a.DM_ZIP_TO_REGION_HIERARCHY_KEY) over (partition by a.POL_FACT_POLICY_TRANSACTIONS_KEY, a.L_LOC_NUM

                                        order by sum(B.COV_AMT) desc

                                        null last) as building_rank

It appears that SQL does not have the FIRST() or FIRST_VALUE() functions and also the "null last" is invalid. I can rank using:

, rank() over (partition by a.POL_FACT_POLICY_TRANSACTIONS_KEY, a.L_LOC_NUM

                                        order by sum(B.COV_AMT) desc

                                        ) as building_rank

To get the right order but then I would still need to select where building_rank = 1 which would require a subquery which I would like to avoid. Are you aware of some method to do this within a single function call?

<snip>

Confidentiality Note: This message contains information that may be confidential and/or privileged. If you are not the intended recipient, you should not use, copy, disclose, distribute or take any action based on this message. If you have received this message in error, please advise the sender immediately by reply email and delete this message. Although ICAT Managers, LLC, Underwriters at Lloyd's, Syndicate 4242, scans e-mail and attachments for viruses, it does not guarantee that either are virus-free and accepts no liability for any damage sustained as a result of viruses. Thank you.

Other related posts: