Daniele Pani Daniele Pani - 1 month ago 10
MySQL Question

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

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

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