Try something like this:
select distinct tempcheck_id, record_id
from #tempcheck
inner join
(select tempcheck_id, min(record_id) as min_id
from #tempcheck
group by tempcheck_id) Sub
on main.tempcheck_id = sub.tempcheck_id
and main.record_id = sub.min_id
except
select t1.tempcheck_id, t1.record_id
from #tempcheck t1
inner join #tempcheck t2
on t1.record_id = t2.record_id
and t1.tempcheck_id < t2.tempcheck_id
and t1.item = t2.item
and t1.i_desc = t2.i_desc
and t1.s_desc = t2.s_desc
and t1.m_desc = t2.m_desc
inner join #tempcheck t3
on t1.record_id = t3.record_id
and t2.tempcheck_id < t3.tempcheck_id
and t1.item = t3.item
and t1.i_desc = t3.i_desc
and t1.s_desc = t3.s_desc
and t1.m_desc = t3.m_desc
You may need to play around with the join criteria to get exactly what you want, but it should put you in the right direction.
I noticed you have four of some record IDs. The above code will find if any three of them match, ignoring and unmatched fourth record. If you want to include unmatched fourth records, create a second query that extends the joins by one.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon