Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / SQL

JOIN Instead of Repeating a Subquery

5.00/5 (2 votes)
6 Feb 2011CPOL 6.4K  
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)