I have a table with PK("object", "type", "mid") and this structure:
+-----+----------+-------+---------+-----+
| type| object | value | preview | mid |
+-----+----------+-------+---------+-----+
| t1 | uid-0001 | 2 | p1 | 1 |
| t1 | uid-0002 | 10 | p2 | 1 |
| t2 | uid-0001 | 1 | p1 | 1 |
| t2 | uid-0003 | 5 | p3 | 1 |
| t3 | uid-0005 | 10 | p5 | 2 |
| t3 | uid-0001 | 40 | p1 | 2 |
| t4 | uid-0004 | 5 | p4 | 2 |
+-----+----------+-------+---------+-----+
The result I would like to achieve is:
+-----+----------+-------+---------+-----+
| type| object | value | preview | mid |
+-----+----------+-------+---------+-----+
| t1 | uid-0002 | 10 | p2 | 1 |
| t2 | uid-0003 | 5 | p3 | 1 |
| t3 | uid-0001 | 40 | p1 | 2 |
| t4 | uid-0004 | 5 | p4 | 2 |
+-----+----------+-------+---------+-----+
The SQL I have tried is:
SELECT *
FROM table
WHERE value in (SELECT max(value) FROM table GROUP BY type)
GROUP BY type
But this fails because it gets an incorrect value when it`s more than one identical value in table.
I have also tried
SELECT *
FROM (SELECT * FROM table ORDER BY value desc) x
GROUP BY type;
but it has same error.
Example of incorrect results:
+-----+----------+-------+---------+-----+
| type| object | value | preview | mid |
+-----+----------+-------+---------+-----+
| t1 | uid-0002 | 10 | p2 | 1 |
| t2 | uid-0003 | 5 | p3 | 1 |
| t3 | uid-0005 | 10 | p5 | 2 |
| t4 | uid-0004 | 5 | p4 | 2 |
+-----+----------+-------+---------+-----+
In this case rows for t3
and t1
used same value (10, that must be for t1) and selected it in both types (t1, t3);