January 16th, 2013, 04:18 AM
Return Last Row by Each ContactID
I dont know if this is possible.
I am trying to run a query that will show me the last row in a table for each individual contactid in a given account.
SELECT contacitd, completeddate FROM activities where accountid = 'AAXA-SZMY8K' ORDER BY contactid DESC LIMIT 10;
The problem is i dont always know how many contactid's will be in a given accountid, so the DESC LIMIT 10 wont work for me.
Also, the above query returns the last 10 rows of a given accountid regardless of contactid, so it usually returns the last 10 rows for a single contactid rather than show the time/date of the last activity for each contactid.
Im running this to export to a csv file, so any suggestions would be welcome.
January 16th, 2013, 05:38 AM
If you only need contactit and completedate you should be able to get it with this statement (not tested):
If you need more information, you will have to make a (self-)join based on the id and date.
SELECT contacitd, MAX(completeddate)
WHERE accountid = 'AAXA-SZMY8K'
GROUP BY contactid