MySQL: Select the highest ID for every row with the same value on another column

Question

suppose i have 2 related tables. The 1st one, named a, has these columns:

activityid
subject
date

The 2nd one, named b has these columns

activityid
crmid

activityid is what relates a and b.

I need to check for duplicate values, being multiple values having the same crmid on the table b. Using the following query:

SELECT y.activityid, y.subject 
FROM vtiger_activity y 
INNER JOIN (SELECT DISTINCT a.activityid, a.crmid 
            FROM vtiger_seactivityrel a, vtiger_seactivityrel b 
            WHERE a.crmid = b.crmid 
            AND a.activityid != b.activityid) z 
ON y.activityid = z.activityid

i can successfully get all of these. Now i want to get all the values with the highest activityid between those with the same crmid. To explain better, if i have 3 rows with crmid = 20 and 4 rows with crmid=21, i want the highest activityid with crm=20, but also the highest activityid with crmid=21. The following:

SELECT y.activityid, y.subject 
FROM vtiger_activity y 
INNER JOIN (SELECT DISTINCT a.activityid, a.crmid 
            FROM vtiger_seactivityrel a, vtiger_seactivityrel b 
            WHERE a.crmid = b.crmid 
            AND a.activityid != b.activityid) z 
ON y.activityid = z.activityid 
WHERE y.activityid = (SELECT MAX(activityid) 
                        FROM (SELECT y.activityid, y.subject 
                            FROM vtiger_activity y 
                            INNER JOIN (SELECT DISTINCT a.activityid, a.crmid 
                                        FROM vtiger_seactivityrel a, vtiger_seactivityrel b 
                                        WHERE a.crmid = b.crmid AND a.activityid != b.activityid) z 
                            ON y.activityid = z.activityid) t)

can only give me the highest activityid overall. Plus, i want to delete all duplicates but those with the highest activityid. What should be the right queries to do these tasks? Thanks anyone.

For those who wonder what is it, it's vtigercrm.

Answer 1

Try this:

SELECT y.activityid, y.subject 
FROM vtiger_activity y 
INNER JOIN (SELECT DISTINCT a.activityid, a.crmid 
        FROM vtiger_seactivityrel a, vtiger_seactivityrel b 
        WHERE a.crmid = b.crmid 
        AND a.activityid != b.activityid) z 
ON y.activityid = z.activityid 
INNER JOIN (
  SELECT m.crmid,MAX(m.activityid) AS activityid 
  FROM ivtiger_seactivityrel m 
  GROUP BY m.crmid) ma
ON ma.activityid=y.activityid