Recupero recordo con campo "nomeCampo" uguale

USE tempdb;

CREATE TABLE dbo.foo(
fooID int NOT NULL PRIMARY KEY,
FirstName varchar(10) NOT NULL,
LastName varchar(10) NOT NULL,
[Address] varchar(25) NOT NULL
);

INSERT dbo.foo
VALUES (1, 'mario', 'rossi', 'via tal dei tali')
     , (2, 'pippo', 'pluto', 'via delle vie')
     , (3, 'alex', 'rossi', 'via delle vecchie vie');

WITH CTE_GetDuplicatedLastNames AS
(
    SELECT LastName
    FROM dbo.foo
    GROUP BY LastName
    HAVING COUNT(*) > 1
)
SELECT F.*
FROM dbo.foo AS F
JOIN CTE_GetDuplicatedLastNames AS D
ON F.LastName = D.LastName
ORDER BY F.fooID;    

/* Output:

fooID       FirstName  LastName   Address
----------- ---------- ---------- -------------------------
1           mario      rossi      via tal dei tali
3           alex       rossi      via delle vecchie vie

(2 row(s) affected)

*/

DROP TABLE dbo.foo;