Group by Id with greatest value-CodePudding

I have a simple table where I'm calculating a "level value" dynamically via SELECT with repeated Ids in this table, what I'm trying to do is to Group the rows Ids by the greatest level value.

Fiddle: https://www.db-fiddle.com/f/2Pyfi2PMV8eaQbDt2uWQjc/2

I already tried using CASE in Group and Order but does not work well.

I already tried using MAX() but I get the score of the second value instead of the fourth. What I'm trying to get is a result like:

| id | score   | level |
| -- | ------- | ----- |
| 1  | []      | 0     |
| 16 | [1,2,4] | 3     |
| 17 | [1]     | 1     |

Is there any way to group the Ids but in case there's a repeated Id get the one with the greatest level?

Thanks.

CodePudding user response：

You can use group by like this :

select test.id , min(test.score) as score,
 (CASE
  WHEN JSON_LENGTH(min(test.score)) = 3 THEN 3
  WHEN JSON_LENGTH(min(test.score)) = 2 THEN 2
  WHEN JSON_LENGTH(min(test.score)) = 1 THEN 1
  ELSE 0
  END) as level
  from test group by test.id

and the result is :

|id | score   | level |
|---|---------|-------|
|1  | []      | 0     |
|16 | [1,2,4] | 3     |
|17 | [1]     | 1     |