JOIN Instead of Repeating a Subquery





5.00/5 (2 votes)
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), ...
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