CODEWITHSUNDEEP
oracle-databaseplsql
Sebastian Hubard
Sebastian Hubard

Reputation: 163

How to delete older rows with no change since previous row by ID

I have a single table with three columns: ID, Score, and Date. 

     Current Data
|---------------------|------------------|------------------|
|          ID         |       Score      |       Date       |
|---------------------|------------------|------------------|
|          123        |         3        |       05/01      |
|---------------------|------------------|------------------|
|          123        |         2        |       04/25      |
|---------------------|------------------|------------------|
|          123        |         2        |       04/20      |
|---------------------|------------------|------------------|
|          123        |         1        |       04/15      |
|---------------------|------------------|------------------|
|          123        |         2        |       04/10      |
|---------------------|------------------|------------------|
|          123        |         1        |       04/05      |
|---------------------|------------------|------------------|
|          456        |         6        |       04/30      |
|---------------------|------------------|------------------|
|          456        |         5        |       04/20      |
|---------------------|------------------|------------------|
|          456        |         4        |       04/10      |
|---------------------|------------------|------------------|
|          456        |         4        |       04/05      |
|---------------------|------------------|------------------|
|          456        |         6        |       04/01      |
|---------------------|------------------|------------------|

     Desired Table
|---------------------|------------------|------------------|
|          ID         |       Score      |       Date       |
|---------------------|------------------|------------------|
|          123        |         3        |       05/01      |
|---------------------|------------------|------------------|
|          123        |         2        |       04/25      |
|---------------------|------------------|------------------|
|          123        |         1        |       04/15      |
|---------------------|------------------|------------------|
|          123        |         2        |       04/10      |
|---------------------|------------------|------------------|
|          123        |         1        |       04/05      |
|---------------------|------------------|------------------|
|          456        |         6        |       04/30      |
|---------------------|------------------|------------------|
|          456        |         5        |       04/20      |
|---------------------|------------------|------------------|
|          456        |         4        |       04/10      |
|---------------------|------------------|------------------|
|          456        |         6        |       04/01      |
|---------------------|------------------|------------------|

I'm trying to only show rows where the value has changed for the ID. Currently I'm using the below code, however, the code currently only groups the data which excludes when the value goes from 1 to 2 to 1 to 2 (I want to show all four rows/values but it only shows the latest 1 and 2).

select * 
from Current_Table 
group by ID, Score

Any help would be appreciated. Thanks

Upvotes: 0

Views: 31

Answers (1)

forpas
forpas

Reputation: 164139

With LEAD() analytical function:

select t."ID", t."Score", t."Date"
from (
  select t.*, lead(t."Score") over (partition by ID order by "Date") next
  from tablename t
) t
where t.next <> t."Score" or t.next is null
order by t."ID", t."Date" desc

See the demo.
Results:

ID  Score   Date
123 3       2020-05-01
123 2       2020-04-25
123 1       2020-04-15
123 2       2020-04-10
123 1       2020-04-05
456 6       2020-04-30
456 5       2020-04-20
456 4       2020-04-10
456 6       2020-04-01

Upvotes: 1

Related Questions