Select the max value from array in mysql query

Question

hi i have the following table (battles) :

+-----------+------------------------------------------+
| id        | battles                                  |
+-----------+------------------------------------------+
| 1         | 1;2                                      |
| 2         | 231;2                                    |
| 3         | 3330;0                                   |
| 4         | 11;333                                   |
| 5         | 32;3324                                  |
| 7         | 2;1                                      |
| 8         | 333:233                                  |
+-----------+------------------------------------------+

The table contains the win and loss of each player (win;lose)

how can i select the biggest win (3330;0)

But is the biggest win the highest `win` value or the highest `win-lose` value? — Nick, Oct 02 '19 at 23:43
I have to split battles to win / lose then I chose the highest win — alex555, Oct 02 '19 at 23:47

score 0 · Accepted Answer · edited Oct 03 '19 at 06:56

0

Your last entry must be

| 7 | 333;233

to work properly. i believe it is only a typo

SELECT id,battles, maximal
From battles t inner join
(Select 
MAX(SUBSTRING_INDEX(battles,';',1)+0) maximal
FRom battles) t1 on t1.maximal = SUBSTRING_INDEX(t.battles,';',1);

The result is

d   battles     maximal
3   3330;0      3330

If you had more ids with 3300 then they would also appear in the result

edited Oct 03 '19 at 06:56

Strawberry

33,338
13
38
57

answered Oct 02 '19 at 23:31

nbk

31,930
6
24
40

hi thanks but what is that for (maximal) – alex555 Oct 02 '19 at 23:48
something wrong with the query – alex555 Oct 03 '19 at 00:01
maximal is the biggest win in your battles column which you defined as win;lose so that id number 3 has 3300 wins and 0 losses. – nbk Oct 03 '19 at 00:11
the query did not work – alex555 Oct 03 '19 at 00:20
Of course it does see http://sqlfiddle.com/#!9/e19e8c/1 – nbk Oct 03 '19 at 00:27
It gives me 9 when the highest win is 500 (I have more than 1,700 values in the table) – alex555 Oct 03 '19 at 01:08
I wrote in my answer that with my query you get every id that has the highest win. so specify another characteristic to minimize or Limit 1 at the end so that it returns only 1. – nbk Oct 03 '19 at 01:09
@alex555 _"It gives me 9 when the highest win is 500"_ is because the query treated value as string. You can try using cast in the subquery like for example `MAX(CAST(SUBSTRING_INDEX(battles,';',1) AS INT))` – FanoFN Oct 03 '19 at 02:29
This is exactly what I was looking for thanks – alex555 Oct 03 '19 at 12:06

Select the max value from array in mysql query

1 Answers1