BSanders BSanders - 1 year ago 44
SQL Question

How can I pull back only records where column has matching data?

I need to pull back data from a table where a column has matching items. This is what I have so far and it doesn't seem to be working.

DB_NAME() AS [DataBase],
co, id,
oldValue, newValue
Table1 T1
AND column1 = 'name1'
AND column2 IN ('name2','name3')
AND (SELECT COUNT(*) FROM Table1 T2 WHERE T1.newValue = T2.newValue )>3

This is a table made up of changes that happen from triggers. I need to be able to query this table and pull back any items that match in the criteria.

Here would be an example of the table

instance | database | co | id | changedby | oldvalue | newvalue
sql01 | 123 | abc | 01 | user1 | 1234 | 5678

There could be millions of these type of records daily. What I need to pull back are only rows where newvalue is the same on 4 or more rows. Since this database is full of personal information (I work for a company that deals with banking) I'm looking for rows where bank routing and accounting may have been fraudulently changed.

Answer Source

You can do this with aggregate window function:

;with cte as(select *, count() over(partition by newvalue) as rn
             from table1 
             where ...)
select * 
from cte
where rn > 3

Note: you should remove this from where clause:

AND (SELECT COUNT(*) FROM Table1 T2 WHERE T1.newValue = T2.newValue )>3