1

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

Rick James
  • 135,179
  • 13
  • 127
  • 222
Сергей Рыбаков
  • 29
  • 3

3 Answers3

1

Your first attempt is really close, you just need to add a where clause to your subquery to link it to the outer query, rather than use GROUP BY, the following will get your desired results:

SELECT t.Type, t.Object, t.Value, t.Preview 
FROM Table AS t
WHERE value IN (SELECT max(value) FROM Table AS t2 WHERE t2.Type = t.Type) 
ORDER BY t.Type;

Example on SQL Fiddle

If you are using a more recent version of MySQL (8.0) you can also use ROW_NUMBER():

SELECT t.Type, t.Object, t.Value, t.Preview
FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY Type ORDER BY Value DESC) AS rowNumber
      FROM Table) AS t
WHERE t.RowNumber = 1
ORDER BY t.Type;

Example on DB<>Fiddle

GarethD
  • 68,045
  • 10
  • 83
  • 123
1

You could create a table with just the type and max value for each type, then use that table as a filter to only see the records with the max value

SELECT *
FROM table t
JOIN (
    SELECT
        type
        ,MAX(value) as max_value
    FROM table
    ) m on t.type = m.type and t.value = m.max_value
cpalmer
  • 311
  • 2
  • 9
0

You can use below query for the optimal solution & make sure the data type of "Value" column should be "Number".

 SELECT * FROM table WHERE (type,VALUE) IN (SELECT type,MAX(value) FROM table GROUP BY type)
monika
  • 67
  • 7