Aggregate function question....
- From: "Sweetser, Joe" <JSweetser@xxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 25 May 2011 19:57:03 +0000
....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:
