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.

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Nov 01 '16 at 23:41
@Strawberry It's because of not providing sample data set? Because otherwise...well thanks for the hint. — Daniele Squalo, Nov 02 '16 at 14:23

score 0 · Accepted Answer · answered Nov 01 '16 at 21:00

0

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

answered Nov 01 '16 at 21:00

PeterHe

2,766
1
8
7

SELECT y.activityid, y.subject FROM vtiger_activity y INNER JOIN (SELECT MAX(a.activityid) AS activityid, a.crmid FROM vtiger_seactivityrel a, vtiger_seactivityrel b WHERE a.crmid = b.crmid AND a.activityid != b.activityid GROUP BY a.crmid) z ON y.activityid = z.activityid – PeterHe Nov 01 '16 at 21:02
This works, although i need to read it carefully to understand what you've done! Now i will try to delete all but these 2 – Daniele Squalo Nov 01 '16 at 21:04

score 0 · Answer 2 · answered Nov 01 '16 at 21:34

Ok. After playing with PeterHe's solution, i found a solution to delete everything but those values:

DELETE vtiger_activity.*, vtiger_seactivityrel.*
FROM vtiger_activity
INNER JOIN vtiger_seactivityrel
ON vtiger_activity.activityid = vtiger_seactivityrel.activityid
WHERE vtiger_activity.activityid NOT IN (
    SELECT activityid FROM(
        SELECT m.crmid,MAX(m.activityid) AS activityid 
        FROM vtiger_seactivityrel m 
        GROUP BY m.crmid
    ) z
)

Thanks.

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

2 Answers2