0

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;
Jhonny
  • 162
  • 9

1 Answers1

0

Use max of picco by grouping it with regione

Satya Singh
  • 11
  • 1
  • An example, or a modification of the OP's code would go a long way in turning this comment into a Answer – RiggsFolly Jul 11 '20 at 15:10
  • In this way I have one row for each region, but The date column is not synchronized with the picco value, because all row have the same date and it is incorrect – Jhonny Jul 11 '20 at 15:19