Here is a way of getting a list of Employees with the SAME (not similar) name.
An alternative to the other solutions using the Over clause with the Row_Number.
This one uses the Count aggregate function on the Over clause.
with Emp as (
select
'FARAH' ename,
'RESEARCH' dept_name,
'ANALYST' designation,
30000 salary,
convert(datetime, 'Dec 03, 1991') date_of_join
union all select 'TOMAR', 'SALES', 'SALESMAN', 15000, convert(datetime, 'Sep 08, 2001')
union all select 'SAXENA', 'SALES', 'SALESMAN', 12500, convert(datetime, 'Sep 28, 1999')
union all select 'TOMAR', 'SALES', 'SALESMAN', 14500, convert(datetime, 'Feb 22, 1997')
union all select 'ANAND', 'RESEARCH', 'CLERK', 11000, convert(datetime, 'Jan 12, 1993')
)
select *
from (
select
count(*) over (partition by e.ename) SameNameCnt,
*
from Emp e
) EmpWithNamCnt
where EmpWithNamCnt.SameNameCnt > 1
order by
ename,
date_of_join
;
Hope that helps out :)
OVER Clause:
https://msdn.microsoft.com/en-gb/library/ms189461.aspx[
^]