I have some data like this ↓
T_ID T_PERIOD T_COUNT T_SUM T_UPDATE_COUNT 1 2013-2014 3436 20118043 0 2 2014-2015 4298 27101356 0 3 2015-2016 5577 38844640 0 4 2016-2017 5764 40701339 0 5 2017-2018 6997 54316874 0 6 2018-2019 13315 151012820 0 7 2019-2020 13933 162731044 0 8 2018-2019 13300 150000000 1 9 2013-2014 3600 21000000 1 10 2018-2019 13500 155000000 2
This table only has insert,during insert T_UPDATE_COUNT = max(T_UPDATE_COUNT) + 1 ;
I want the data to look like this ↓
T_ID T_PERIOD T_COUNT T_SUM T_UPDATE_COUNT 9 2013-2014 3600 21000000 1 2 2014-2015 4298 27101356 0 3 2015-2016 5577 38844640 0 4 2016-2017 5764 40701339 0 5 2017-2018 6997 54316874 0 10 2018-2019 13500 155000000 2 7 2019-2020 13933 162731044 0
How do i write SQL statement ?
Advertisement
Answer
Assuming that you need to get all the rows with the maximum value of T_UPDATE_COUNT
for each T_PERIOD
, you may try:
select T_ID, T_PERIOD, T_COUNT, T_SUM, T_UPDATE_COUNT from ( select T_ID, T_PERIOD, T_COUNT, T_SUM, T_UPDATE_COUNT, row_number() over (partition by T_PERIOD order by T_UPDATE_COUNT desc) as RN from yourData x ) where RN = 1
There are different ways to do this; I believe this one is clear enough: the inner query is used to compute the row number in the set of all the rows with the same value of T_PERIOD
(partition by T_PERIOD
) and name it RN
.
The external one simply filters this result to only get the first rows (RN = 1
) of each group.