How can I get distinct IDs based on presents of specific values in another column?

user_14065682 0
Viewed 33

I have a table with a couple thousend unique IDs. Every ID has around 200-300 EventCodes. Here is an example table:

ID EventCode
1 A01G0
1 B428
2 N0030
2 B428
3 CD45
3 B428

I would like to get the distinct ID when specific EventCodes are not present. For example, when "A01G0" or "N0030" are present, skip the ID 1 and 2.

Desired Output should then be:

ID
3

I found this solution but it is not suitable for daily use because of lack of speed:

WITH    cte
        AS (SELECT
        ev.id as id
        ,ev.EventCode AS EventCode
        ,ev.EventDate

        ,ROW_NUMBER() OVER ( PARTITION BY ev.id ORDER BY ev.EventDate DESC) AS rn
        FROM eventTable ev
        WHERE NOT EXISTS( SELECT *
                          FROM eventTable 
                          WHERE ev.id = ev.id
                                AND ev.id NOT IN ('A01G0','A01G2','N0030','N0032','UN030')
                                ))
        SELECT id
        FROM cte
        WHERE rn = 1

Any ideas a a simpler approach would be really appreciated!

tsql sql-server sql
1 Answers

One method is aggregation:

select id
from eventTable
group by id
having sum(case when event_code in ('A01G0', 'N0030') then 1 else 0 end) = 0;

A more efficient method would assume that you are starting with a table that has one row per id. Then you would use use not exists:

select i.id
from id_table i
where not exists (select 1
                  from eventtable et
                  where et.id = i.id and
                        et.event_code in ('A01G0', 'N0030')
                 );

In particular, this can make use of an index on eventtable(id, event_code) and so should be quite fast.

If you don't have a table with one row per id, then I suspect that you have an issue with your data model. In general, an entity worthy of an id is worthy of having its own table.

user_11440350
Related