beba beba - 3 months ago 20
MySQL Question

MySQL - Select max revision within nested statement

I would like to select max revision drawings from mysql table.
I can't use anything but nested select statement(?) (all conditions should be after 'SELECT * FROM

drawings
').

So, the 'drawings' table is:

+----+---------+-------------+-------+---------+---------------------+
| id | number | title |format | revision| date |
+----+---------+-------------+-------+---------+---------------------+
| 100| 022588 | some title | dwg | 1 | 2016-01-07 08:00:00 |
| 101| 022588 | some title | dwg | 2 | 2016-01-07 08:01:00 |
| 103| 022588 | some title | pdf | 3 | 2016-01-07 08:15:32 |
| 104| 022588 | some title | dwg | 3 | 2016-01-07 09:10:32 |
+----+---------+-------------+-------+---------+---------------------+


Result I would like to get is (same number, largest revision for appropriate format):

| 103| 022588 | some title | pdf | 3 | 2016-01-07 08:15:32 |
| 104| 022588 | some title | dwg | 3 | 2016-01-07 09:10:32 |


And once more, I have (must) to start query with 'SELECT * FROM
drawings
WHERE ......'.

Last thing I tried were:

SELECT * FROM `drawings` WHERE `revision` IN ( SELECT MAX(`revision`) FROM `drawings` GROUP BY `number`, `format` ) GROUP BY `number`, `format` ORDER BY `number` DESC;


... and I got an proper pdf and wrong/lowest dwg (1 instead of 3).

Answer

This is the query:

select *
from drawings a inner join (select number, title, format, 
                            max(revision) as revision
                            from drawings 
                            group by number, title, format) b 
on a.number = b.number and a.title = b.title and a.format = b.format
and a.revision = b.revision