I don't know why are you trying to join table2 if it stores the same values as table1 (
hub_id
)?
Have a look here:
DECLARE @table1 TABLE (address_id INT, hub_id INT, [name] VARCHAR(30))
INSERT INTO @table1 (address_id, hub_id, [name])
SELECT 100, 20, 'anurag'
UNION ALL SELECT 100, 20, 'abhishek'
UNION ALL SELECT 100, 20, 'rakesh'
UNION ALL SELECT 200, 50, 'nitesh'
UNION ALL SELECT 200, 50, 'neha'
SELECT ROW_NUMBER() OVER (ORDER BY address_id) AS RowNo, *
FROM (
SELECT DISTINCT address_id, hub_id
FROM @table1
) AS T
Result:
RowNo address_id hub_id
1 100 20
2 200 50