asked
Not getting Distinct column as max date.
Can you see what is wrong with this query. or how to get only one Lookupid list I get multiple same lookup id in output. select V_CONSTITUENT.LOOKUPID , V_CONSTITUENT.NAME as ConstituentName , V_CONSTITUENT.NICKNAME as NickName , V_CONSTITUENTADDRESS.ADDRESSBLOCK as Address , V_CONSTITUENTADDRESS.CITY , V_CONSTITUENTADDRESS.STATEID_ABBREVIATION as State , V_CONSTITUENTADDRESS.POSTCODE as Zipcode , V_PROSPECTPLAN.NAME as ProspectPlanName , V_PROSPECTPLAN.ADDEDBY_USERNAME , V_PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID , V_PROSPECTPLAN.STARTDATE , V_PROSPECTPLAN.CHANGEDBY_USERNAME , V_OPPORTUNITY.ASKAMOUNT , V_OPPORTUNITY.EXPECTEDASKAMOUNT , V_OPPORTUNITY.LIKELIHOODPERCENT , max(V_REVENUE.DATE) as LastGiftDate , V_REVENUE.AMOUNT , V_REVENUE.APPEALID , V_REVENUE.SOURCECODE from V_QUERY_CONSTITUENT V_CONSTITUENT left outer join V_QUERY_REVENUE as V_REVENUE on
V_CONSTITUENT.ID = V_REVENUE.CONSTITUENTID left outer join V_QUERY_CONSTITUENTADDRESS as V_CONSTITUENTADDRESS on
V_CONSTITUENT.ID = V_CONSTITUENTADDRESS.CONSTITUENTID left outer join V_QUERY_PROSPECTPLAN as V_PROSPECTPLAN on
V_CONSTITUENT.ID = V_PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID left outer join V_QUERY_OPPORTUNITY as V_OPPORTUNITY on
V_PROSPECTPLAN.ID = V_OPPORTUNITY.PROSPECTPLANID where V_CONSTITUENT.LOOKUPID is not null group by V_CONSTITUENT.LOOKUPID , V_CONSTITUENT.NAME , V_CONSTITUENT.NICKNAME , V_CONSTITUENTADDRESS.ADDRESSBLOCK , V_CONSTITUENTADDRESS.CITY , V_CONSTITUENTADDRESS.STATEID_ABBREVIATION , V_CONSTITUENTADDRESS.POSTCODE , V_PROSPECTPLAN.NAME , V_PROSPECTPLAN.ADDEDBY_USERNAME , V_PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID , V_PROSPECTPLAN.STARTDATE , V_PROSPECTPLAN.CHANGEDBY_USERNAME , V_OPPORTUNITY.ASKAMOUNT , V_OPPORTUNITY.EXPECTEDASKAMOUNT , V_OPPORTUNITY.LIKELIHOODPERCENT --, MAX(V_REVENUE.DATE) as LastGiftDate , V_REVENUE.AMOUNT , V_REVENUE.APPEALID , V_REVENUE.SOURCECODE order by V_CONSTITUENT.NAME
This site runs by you voting. For each helpful answer below, click on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the thumbs up next to that answer.
I see that you're retrieving the MAX date, but it's going to retrieve the max across the entire group. Your GROUP BY statement is returning multiple groups, so you're getting multiple max values. Change it to retrieving a TOP 1 with an order by clause instead.
If we put all other considerations to one side for a second, does the following return the right V_REVENUE.DATE for each V_CONSTITUENT.LOOKUPID? select V_CONSTITUENT.LOOKUPID , max(V_REVENUE.DATE) as LastGiftDate from V_QUERY_CONSTITUENT V_CONSTITUENT left outer join V_QUERY_REVENUE as V_REVENUE on
V_CONSTITUENT.ID = V_REVENUE.CONSTITUENTID where V_CONSTITUENT.LOOKUPID is not null group by V_CONSTITUENT.LOOKUPID order by V_CONSTITUENT.NAME
As you have got multiple columns in GROUP BY clause, you are getting multiple LOOKUPIDs in SELECT query. Limit to just LOOKUPID as part of GROUP BY clause to get unique LOOKUPIDs in SELECT query.
Thank you Venkataraman, How can I use only one Group by since I have multiple select columns from multiple views. I have to include in group as many column I am retrieving right? If I use only one column then it show up this error. Column 'V_QUERY_CONSTITUENT.NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
17 People are following this question.
