SQL max aggregate function

Posted on 2011-04-28
Last Modified: 2012-05-11
I am trying to get the maximum from a result set so I can avoid receiving duplicates rows.  The field in question is totalgifts from the gifts_VIEW_donor_relations_simple table.  The field already contains the total for all gifts given, but it's duplicated for each fund (ie. if someone has given a total of $1000 but split it evenly between the building fund and the athletic fund, there would be two lines in the table with $1000 in each line in the totalgifts column).  The query I have setup is below:

use vcu

select c.coreid as ConstituentCoreID
      ,c.millid_married as MarriedID
      ,n1.namesmashd as BoardNameSort
      ,n1.nameformn as BoardName
      ,n.namesmashd as ConstituentSort
      ,n.nameformn as Constituent
      ,r.relstrtdat as StartDate
      ,r.relstopdat as StopDate
      ,r.rellook1 as Position
      ,r.relisa as Relationship
      ,cn.table_val ConstituentCode
      ,f.table_val as FoundationType
      ,d.deathdate as ConstituentDeathDate
      ,d1.deathdate as DisbandedDate
      ,g.totalgifts as TotalGifts
      ,i.intaddress as EMail
from corebio_VIEW_married_surviving c
      left outer join relation_full r on c.coreid=r.relid
      left outer join corebio_full c1 on r.relrelatid=c1.coreid
      join foundation_types f on c1.corefntype=f.table_code
      left outer join names_VIEW_preferred n on c.coreid=n.nameid
      left outer join names_VIEW_preferred n1 on c1.coreid=n1.nameid
      left outer join death_full d on c.coreid=d.deathid
      left outer join death_full d1 on c1.coreid=d1.deathid
      join constituent_types cn on c.coreconst=cn.table_code
      join gifts_VIEW_donor_relations_simple g on c.coreid=g.coreid      left outer join internetaddress_VIEW_preferred_email_NOT_INVALID i on c.coreid=i.intid
where f.table_type='board'
order by ConstituentSort
Question by:lcallah93
    LVL 40

    Accepted Solution

    Use MAX(g.totalgifts) and GROUP BY the rest of the fields.

    Author Closing Comment

    OMG - that was so EASY!!  Thank you, thank you, thank you!!!
    LVL 40

    Expert Comment

    You are welcome. Glad to help. :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how the fundamental information of how to create a table.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now