How select greatest rows in each types

问题: I have a table with PK("object", "type", "mid") and this structure: +-----+----------+-------+---------+-----+ | type| object | value | preview | mid | +-----+---------...

问题:

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


回答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

回答2:

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


回答3:

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) 
  • 发表于 2020-06-27 19:13
  • 阅读 ( 104 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除