Oracle 10g sql query

Tags: , ,



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 ?

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.



Source: stackoverflow