CODEWITHSUNDEEP
mysql
beba
beba

Reputation: 82

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).

Upvotes: 0

Views: 768

Answers (4)

Walter_Ritzel
Walter_Ritzel

Reputation: 1397

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

Upvotes: -1

Ernesto Herrera Arias
Ernesto Herrera Arias

Reputation: 11

I suggest:

select d1.* from drawings d1 where d1.id in ( select max(d2.id) from drawings d2 group by d2.format )

Querys using PrimaryKey are faster. And you get the last record revision.

Upvotes: 1

Paul Spiegel
Paul Spiegel

Reputation: 31802

You can use a corelated subquery in the WHERE clause:

SELECT d1.*
FROM `drawings` d1
WHERE `revision` = (
    SELECT MAX(`revision`)
    FROM `drawings` d2
    WHERE d2.`number` = d1.`number`
      AND d2.`format` = d1.`format`
) 
ORDER BY `number` DESC;

See it on SQLFiddle

The query will return exactly one row for each combination of number and format with the highest revision. I use that combination because of your original query: GROUP BY number, format. But you also wrote: "largest revision for appropriate format". In this case you should use:

SELECT d1.*
FROM `drawings` d1
WHERE `revision` = (
    SELECT MAX(`revision`)
    FROM `drawings` d2
    WHERE d2.`format` = d1.`format`
) 
ORDER BY `number` DESC;

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

If I read your question correctly, then you want to get drawings from each format group having the maximum revision number in that group. One approach uses a subquery to identify formats and their max revisions, and then uses that subquery to restrict the original drawings table.

SELECT t1.*
FROM drawings t1
INNER JOIN
(
    SELECT format, MAX(revision) AS revision
    FROM drawings
    GROUP BY format
) t2
    ON t1.format = t2.format AND
       t1.revision = t2.revision

Follow the link below for a running demo:

SQLFiddle

Upvotes: 2

Related Questions