Clever Geek Handbook

Comparing sequential strings using oracle

I have a table that looks something like this:

| ID | FROM_DATE | TO_DATE |
------------------------------
| 1  |  1/1/2001 | 2/1/2001|
| 1  |  2/1/2001 | 3/1/2001|
| 1  |  2/1/2001 | 6/1/2001|
| 1  |  3/1/2001 | 4/1/2001|
| 2  |  1/1/2001 | 2/1/2001|
| 2  |  1/1/2001 | 6/1/2001|
| 2  |  2/1/2001 | 3/1/2001|
| 2  |  3/1/2001 | 4/1/2001|

He is already sorted on ID

, From_Date

, To_date

.

What I want to do is delete the lines that are From_Date

earlier than To_date

from the previous line, and the ID is equal to the ID from the previous line. So in this example I would only remove the 3rd and 6th lines .

I know I need some kind of looping structure to accomplish this, but I don't know how as I am actually looking at two lines at a time here. How can I accomplish this in Oracle?

EDIT: where using the "LAG" function is faster and easier, I also remove the 4th and 7th lines , which I don't want to do, For example, when it gets to line 4 , it has to compare "from_date" with "to_date" from line 2 ( instead of line 3 because line 3 should be removed).

+3


source to share


1 answer


You can use a window function lag

to identify these lines:



DELETE FROM mytable
WHERE rowid IN (SELECT rowid
                FROM   (SELECT rowid, from_date, 
                               LAG(to_date) OVER 
                                  (PARTITION BY id
                                   ORDER BY from_date, to_date) 
                                  AS lag_to_date
                        FROM   my_table) t
                WHERE  from_date < lag_to_date)
+4


source






More articles:


All Articles