How can I have entire row of a max value?
Hi I’m on a school project and I can’t find a solution to my problem. I have to do a query where I want the entire row of the table with the max value of the "Picco" column for all regions. The code written by me is:
SELECT DELTA.`data`, DELTA.`regione`, DELTA.Differenza AS Picco FROM (SELECT dr.`data`, dr.`regione`, dr.`deceduti`, dr.`deceduti`-LAG (dr.`deceduti`) OVER (PARTITION BY dr.`regione`) AS Differenza FROM `datareg` dr) AS DELTA; I have this output:
| data | regione | Picco | | 2020-03-08 18:00:00 | Abruzzo | 0 | | 2020-03-09 18:00:00 | Abruzzo | 0 | | 2020-03-10 18:00:00 | Abruzzo | 1 | | 2020-03-11 17:00:00 | Abruzzo | 0 | | 2020-03-12 17:00:00 | Abruzzo | 1 | ... | 2020-04-03 17:00:00 | Abruzzo | 13 | | 2020-04-04 17:00:00 | Abruzzo | 7 | | 2020-04-05 17:00:00 | Abruzzo | 5 | | 2020-04-06 17:00:00 | Abruzzo | 11 | | 2020-04-07 17:00:00 | Abruzzo | 3 | | 2020-04-08 17:00:00 | Abruzzo | 7 | | 2020-04-09 17:00:00 | Abruzzo | 15 |->i want only this row for each region | 2020-04-10 17:00:00 | Abruzzo | 4 | .. I don’t want all rows for each region, but only one with the max value for the ‘Differenza’ column. How can I do? Thank you for your time spent for me.
Thanks to all, I report the solution for my case:
SELECT DELTA.`data`, DELTA.`regione`, MAX(DELTA.Differenza) AS Picco FROM (SELECT dr.`data`, dr.`regione`, dr.`deceduti`, dr.`deceduti`-LAG (dr.`deceduti`) OVER (PARTITION BY dr.`regione`) AS Differenza FROM `datareg` dr ORDER BY dr.`deceduti`-LAG (dr.`deceduti`) OVER (PARTITION BY dr.`regione`) DESC) AS DELTA GROUP BY DELTA.regione; 
Use max of picco by grouping it with regione
