As an alternate, I would like to share the following query using the previous table definition using RANK:
DECLARE @FirstTable table(FirstName varchar(20))
DECLARE @SecondTable table
(ID int,
FirstName varchar(20),
LastName varchar(20),
FirstNameHashCode varchar(20),
FirstNameFrequency int)
INSERT INTO @FirstTable(FirstName) VALUES('Billy')
INSERT INTO @FirstTable(FirstName) VALUES('Super')
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
VALUES(1, 'Billy', 'Mays', '0XB$22', 22)
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
VALUES(2, 'Billy', 'TheKid', '0XB$22', 22)
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
VALUES(3, 'Super', 'Man', 'SJ3JD', 40)
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
VALUES(4, 'Super', 'Friends', 'SJ3JD', 40)
-------------- Here is the query
SELECT
ST.FirstNameHashCode,
ST.FirstNameFrequency,
ST.ID
FROM @FirstTable AS FT
JOIN
(SELECT FirstName, ID, FirstNameHashCode, FirstNameFrequency,
RANK() OVER(PARTITION BY FirstName ORDER BY ID) AS NRANK
FROM @SecondTable
) AS ST ON FT.FirstName = ST.FirstName AND ST.NRANK = 1