Aggregate function question....

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.

--
http://www.freelists.org/webpage/oracle-l