i've got a problem with my mysql request, i have this table :
| col1 | id_sec | _index |
|---|---|---|
| 111 | 20 | 3 |
| 111 | 50 | 0 |
| 111 | 60 | 1 |
| 111 | 20 | 2 |
| 111 | 20 | 1 |
| 111 | 20 | 0 |
| 111 | 60 | 0 |
i would like to have :
| col1 | id_sec | _index |
|---|---|---|
| 111 | 60 | 1 |
| 111 | 50 | 0 |
| 111 | 20 | 3 |
but i have :
| col1 | id_sec | _index |
|---|---|---|
| 111 | 20 | 0 |
| 111 | 50 | 0 |
| 111 | 60 | 0 |
my request is :
select *
from ( select *
from test
WHERE col1 = 111
order by id_sec, _index desc
) t2
group by id_sec
thanks for your help !
CodePudding user response：
SELECT col1, id_sec, MAX(_index) _index
FROM table
GROUP BY 1,2
ORDER BY 2 DESC;
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1e1155442ac7dcf3f54ba1fee5228078