CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
Nick
Nick

Reputation: 2641

Max Value in group that fit where clause

Yes, it is another one of those greatest-n-per-group questions! I have been trying for a few days now trying to work around this problem but to no avail. I have been searching SO too but I don't even know if I am looking in the right place. The most simplified version of the problem as possible is below.

I have 2 tables, one is a many to many and the other has the data. 

--------a-------    ------------b-----------
| id | version |    | id  | version | external_ref |
|  1 |    1    |    |  1  |    1    |       9      |
|  1 |    2    |    |  1  |    2    |       8      |
|  1 |    3    |    |  1  |    3    |       7      |
|  2 |    1    |    |  2  |    1    |       5      |
|  2 |    2    |    |  2  |    2    |       6      |
       .                          .
       .                          .
       .                          .

This is my current (dramatically simplified) query to get the results:

SELECT * FROM a 
    INNER JOIN (
        SELECT MAX(Version) FROM a GROUP BY id
    ) j ON a.id = j.id AND a.version = j.version
    LEFT JOIN b ON a.id = b.id AND a.version = b.version
WHERE  (b.external_ref = 9 
    OR b.external_ref = 8 (ect)
 ) AND (a.id = 1 OR
       a.id = 2)

The problem with this query is that if external_ref = 7 is not in the where clause then no row is being returned.

If I was looking at entries with only external_ref of 8, 9 or 5 then I would like to be able to get the following result

| id | version |   data from table a    |
|  1 |    2    | some data from table a |
|  2 |    1    |   some more data       |

So how do I get the MAX(version) that meets the where clause?

The full query is:

SELECT DISTINCT `t`.`Version`, `t`.`TermID`, `t`.`Definition`, `t`.`Name` 
FROM `term` AS `t`
  INNER JOIN (
    SELECT MAX(`Version`) version, `TermID`
    FROM `term`
    GROUP BY `termID`
) jj ON `t`.`TermID` = jj.`TermID` AND `t`.`Version` = jj.`Version`
LEFT JOIN `syllabusin` AS `si` ON `t`.`TermID` = `si`.`TermID` AND `t`.`Version` = `si`.`Version`
LEFT JOIN `synonyms` AS `sy` ON `t`.`TermID` = `sy`.`TermID`
WHERE
  (`si`.`SyllabusID` = 7  
   OR  `si`.`SyllabusID` = 6  
   OR  `si`.`SyllabusID` = 5  
   OR  `si`.`SyllabusID` = 4  
   OR  `si`.`SyllabusID` = 3  
   OR  `si`.`SyllabusID` = 2  
   OR  `si`.`SyllabusID` = 1  
   OR  `si`.`SyllabusID` = 8 )
AND (  `t`.`Name` LIKE '%term%'  
     OR  `t`.`Acronym` LIKE '%term%'  
     OR  `t`.`Definition` LIKE '%term%'  
     OR  `sy`.`Synonym` LIKE '%term%'  )

EDIT:

To clarify what I want, I want to get the rows from table a which are referenced externally (have external_ref = x) that have the max version for their id.

Upvotes: 1

Views: 108

Answers (1)

Barmar
Barmar

Reputation: 781004

I think this may be it:

SELECT a.*
FROM a
JOIN (
    SELECT a.id, MAX(a.version) AS maxversion
    FROM a
    JOIN b ON a.id = b.id and a.version = b.version
    WHERE b.external_ref IN (9, 8)
    GROUP BY a.id
) AS amax ON a.id = amax.id AND a.version = amax.version

It can also be written like:

SELECT a.*
FROM a
JOIN (
    SELECT a.id, MAX(a.version) AS maxversion
    FROM a
    JOIN (SELECT *
          FROM b
          WHERE external_ref IN (9, 8)) AS b
    ON a.id = b.id and a.version = b.version
    GROUP BY a.id
) AS amax ON a.id = amax.id AND a.version = amax.version

For your more complete query, I think you just have to move all the joins and conditions into the subquery:

SELECT DISTINCT `t`.`Version`, `t`.`TermID`, `t`.`Definition`, `t`.`Name` 
FROM `term` AS `t`
INNER JOIN (
    SELECT MAX(`Version`) version, `TermID`
    FROM `term` AS t
    JOIN syllabusin AS si ON `t`.`TermID` = jj.`TermID` AND `t`.`Version` = jj.`Version`
    JOIN `synonyms` AS `sy` ON `t`.`TermID` = `sy`.`TermID`
    WHERE
      (`si`.`SyllabusID` IN (7, 6, 5, 4, 3, 2, 1, 8)
    AND (  `t`.`Name` LIKE '%term%'  
         OR  `t`.`Acronym` LIKE '%term%'  
         OR  `t`.`Definition` LIKE '%term%'  
         OR  `sy`.`Synonym` LIKE '%term%'  )        
    GROUP BY `termID`
) jj ON `t`.`TermID` = jj.`TermID` AND `t`.`Version` = jj.`Version`

It's basically the same logic as the earlier query: You perform a join with all the conditions, and get the max version per ID from that. Then you join with the original table, selecting just the rows that match the ID and max version.

Upvotes: 2

Related Questions