Subject Re: Re[2]: [firebird-support] Find duplicate multi-row entries
Author Svein Erling Tysvær
Post date 2017-09-28T06:53:48Z
Sure it is possible to write such a query:

select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from routes r
left join route_history rh1 on r.node = rh1.node
                          and r.route_index = rh1.route_index
                          and r.link_node = rh1.link_node
                          and r.quality is not distinct from rh1.quality
left join route_history rh2 on rh1.node = rh2.node
                          and rh1.route_index = rh2.route_index
                          and rh1.route_updated < rh2.route_updated
where rh1.node is null 
  and rh2.node is null

I use two LEFT JOINS because I assume you want things to be returned if things are changed back, e.g. if you have

5557    1    1116    0
5557    1    1116    0

then you only want one row in route_history, but if you have

5557    1    1116    0
5557    1    1116    1
5557    1    1116    0

then you don't want the last record to be considered a duplicate, but know when it changed back. If ROUTE_INDEX is just a running number, you consider

5557    1    1116    0
5557    2    1116    0

to be duplicates, and there are no gaps between the ROUTE_INDEX for each NODE, then the query can be simplified:

select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from routes r
left join route_history rh1 on r.node = rh1.node
                          and r.route_index+1 = rh1.route_index
                          and r.link_node = rh1.link_node
                          and r.quality is not distinct from rh1.quality
where rh1.node is null 

Note that I use IS NOT DISTINCT so that NULLs are considered equal. If you prefer, the left joins can be replaced by (nested) NOT EXISTS.

HTH,
Set

2017-09-28 7:22 GMT+02:00 'Daniel Miller' dmiller@... [firebird-support] <firebird-support@yahoogroups.com>:


On 9/27/2017 9:20:54 PM, "liviuslivius liviuslivius@... [firebird-support]" <firebird-support@yahoogroups. com> wrote:

group by + having
is your friend.
E.g.
Select field1, field2, count(*)
From tablex
Group by field1, field2
Having count(*)>1

But in your scenario i do not know if this is the solution. Question is if record was changed and in next update "restored" and once again changed is this duplicate or no?
If yes above query is ok if no then procedure is your friend.

And for the future change your audit trigger and check before if there was any change

Thank you, but I this isn't quite what I need.  I need to match against all the records of a set (up to 8 rows per set) - not just individual rows.

--
Daniel