I may have misunderstood what you were looking for.

This should get you the first four rows you wanted. The derived table x will include distinct copies of only those rows that are duplicates. You have to join back to the sopurce table to get all the rows.

SELECTd.room_name,

d.session_name,

d.event_date,

d.delegate

FROM@data d

JOIN (SELECTroom_name,

session_name,

event_date,

delegate

FROM@data

GROUP BY

room_name,

session_name,

event_date,

delegate

HAVINGCOUNT(*) > 1) x

ON d.room_name = x.room_name

AND d.session_name = x.session_name

AND d.event_date = x.event_date

AND d.delegate = x.delegate