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' [email protected]
[firebird-support] <[email protected]>:
>
>
> On 9/27/2017 9:20:54 PM, "liviuslivius [email protected]
> [firebird-support]" <[email protected]> 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
>
>
>
>
