I have, for example, this table:
| id | name | campagna | valore |
|---|---|---|---|
| 1 | u1 | 1 | 22 |
| 2 | u1 | 2 | 23 |
| 4 | u2 | 1 | 223 |
| 5 | u3 | 2 | 223 |
I would select rows that have name value unique(distinct) but the row that has max(campagna) between the rows with same name . For example I would have :
| name | campagna | valore |
|---|---|---|
| u1 | 2 | 23 |
| u2 | 1 | 223 |
| u3 | 2 | 223 |
Can I do it? Thanks.
You want one result row per name. The row to pick per name is the one with the maximum capagna for the name.
This is usually done with an analytic function, e.g.:
select name, campagna, valore
from
(
select t.*, max(campagna) over (partition by name) as max_campagna
from mytable t
)
where campagna = max_campagna
order by name;
But it can be achieved with other methods, too. E.g.:
select *
from mytable
where (name, campagna) in
(
select name, max(campagna)
from mytable
group by name
)
order by name;
or
select *
from mytable t
where not exists
(
select null
from mytable t2
where t2.name = t.name
and t2.campagna > t.campagna
)
order by name;
