List of employees for example. Quick way to find duplicate values using LAG windowing function
;with c as (
SELECT [FirstName]
,[LastName],
[FirstName] + ' ' + [LastName] x
,[StaffNumber]
FROM Employee
),
cc as(
SELECT FirstName, LastName, StaffNumber, x , lag (x) over (partition by LastName order by x) dup
from c)
select FirstName, LastName, StaffNumber, x , dup From cc
where x=dup
order by LastName
