remove duplicate values by date [message #666850] |
Tue, 28 November 2017 14:23 |
|
laredoeneko
Messages: 21 Registered: November 2017
|
Junior Member |
|
|
Hello
I have a table in which a process inserts the status of some elements and records the insertion date for each execution. this procedure inserts one row per element 1 time a day, but sometimes the process fails and inserts on the same day 2 times the same register (with different values) being valid only the last one.
something like this:
Element status insert_date
a UP 11/27/2017 13:00
b UP 11/27/2017 13:00
c UP 11/27/2017 13:00
a UP 11/28/2017 13:00
b UP 11/28/2017 13:00
c UP 11/28/2017 13:00
a DOWN 11/28/2017 13:05
b UP 11/28/2017 13:05
c DOWN 11/28/2017 13:05
I wanna a query to obtain the last differents rows of the last execution, normally with sysdate -1 in query it is valid, but when the process fail the i obtain 2 execution for this days, but i wanna only the last execution
result:
a DOWN 11/28/2017 13:05
b UP 11/28/2017 13:05
c DOWN 11/28/2017 13:05
my table has around 4 millions rows day by day.
thank you very much for your help
|
|
|
|
|
|
Re: remove duplicate values by date [message #666871 is a reply to message #666857] |
Wed, 29 November 2017 14:43 |
|
laredoeneko
Messages: 21 Registered: November 2017
|
Junior Member |
|
|
thank you barbara, works fine, but i have one question.
what is the differente between:
select element,
max (status) keep (dense_rank last order by insert_date) status,
max (insert_date) insert_date
from your_table
group by element
order by insert_date, element
and this other:
select element,
status,
max (insert_date) insert_date
from your_table
group by element
order by insert_date, element;
i think that in my case, the result is the same, i have the values for the last execution.
best regard
César
[Updated on: Wed, 29 November 2017 14:44] Report message to a moderator
|
|
|
|
|
|
Re: remove duplicate values by date [message #667026 is a reply to message #666882] |
Tue, 05 December 2017 07:10 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
why not stop it from ever happening?
create unique index test_u1 on test(element,status,trunc(insert_date));
You will only be allowed one entry for each element and Up/DOWN per date.
|
|
|