Omnus Ruthius Omnus Ruthius - 5 months ago 19
SQL Question

Oracle SQL - How do I sequentially iterate through this result set to remove certain records?

My current output is shown in the image below. I highlighted the records I want removed.

Current Output

I am primarily a C# developer, so my "pseudo-logic" to get the results I want is as follows:

List<int> resultSet = new List<int>();

for(int i = 1; i < table.Length; i++)
{
if((c_id[i] == c_id[i-1])&&
(sub_id[i] < sub_id[i-1]))
{
resultSet.Remove(contact_event_id[i]);
resultSet.Remove(contact_event_id[i-1]);
}
}


Essentially, what I want done is to remove records from the result set that are "paired" by c_id. Two records are considered a pair if:


  • Record i and record i-1 have the same c_id

  • Record i has a sub_id that's less than record i-1



A few important notes:


  1. I only have read access to this database/table.

  2. The ce_id is the primary key of this table, which we'll call e_table

  3. Do not rely on the e_date for ordering...use ce_id.



The SQL I have so far is below. Notice the "..." in the having clause; this is to keep things as simple as possible for the implementation. There is a lot more logic involved with this task that isn't worth getting into unless it's really needed.

SELECT * FROM
(
SELECT * FROM e_table
WHERE e_date >= TO_DATE(TRUNC(SYSDATE-1))
AND e_date <= TO_DATE(TRUNC(SYSDATE-1) || ' 23:59:59', 'DD-MON-YY HH24:MI:SS')
AND sub_id IN(2, 1)
) ce
WHERE ce.c_id IN
(
select c_id
from e_table
where e_date >= TO_DATE(TRUNC(SYSDATE-1))
AND e_date <= TO_DATE(TRUNC(SYSDATE-1) || ' 23:59:59', 'DD-MON-YY HH24:MI:SS')
group by c_id
having ...
)
ORDER BY ce_id ASC;


Any help is greatly appreciated. Please let me know if any elaboration is needed.

Edit as pointed out by MT0 in the comments:
Consider the following record set:
enter image description here

Notice how, with my current proposed logic, the two middle records are considered pairs. They should be removed; no question about it. However, see afterwards how, when removed, the remaining two rows also meet the requirements of being pairs at first glance? They should not be considered as such as they are not adjacent in the original output. In this case, I suppose the e_date will have to be utilized after all. However, it is important to note that the times of these pairs, while seemingly identical, can differ by up to one second. No matter what, the ordering still needs to be done with the ce_id, as ordering by the date can cause the sub_id to switch places inappropriately.

vkp vkp
Answer

Calculate the row numbers for each c_id ordered by ce_id.

Then join on the previous row and check to see if the sub_id value is > or <.

Because of a self-join use union all to get ce_id's from both the tables and exclude those rows from the output.

with x as (
select t.*,row_number() over(partition by c_id order by ce_id) rn from t
)
select * from t 
where ce_id not in (    
select x1.ce_id
from x x1
join x x2 on x1.c_id = x2.c_id and x1.rn = x2.rn-1
where x1.sub_id > x2.sub_id
union all
select x2.ce_id
from x x1
join x x2 on x1.c_id = x2.c_id and x1.rn = x2.rn-1
where x1.sub_id > x2.sub_id
)

Sample Demo

Edit: To include the current query of the OP as a cte and using this answer to get the result,

with t as (/*your query here*/)
, x as (
select t.*,row_number() over(partition by c_id order by ce_id) rn from t
)
select * from t 
where ce_id not in (    
select x1.ce_id
from x x1
join x x2 on x1.c_id = x2.c_id and x1.rn = x2.rn-1
where x1.sub_id > x2.sub_id
union all
select x2.ce_id
from x x1
join x x2 on x1.c_id = x2.c_id and x1.rn = x2.rn-1
where x1.sub_id > x2.sub_id
)