Complex join in sql with top 10 row

Question

Table1:
Id  Word    Frequency
1   A   1
2   B   5


Table2:
Id  Word    SecondWord  SecondFrequency
1   A   A1      1           
2   A   A2      5
3   A   A3      10
4   A   A4      9
5   A   A5      20
6   B   B1      5
7   B   B2      8
8   B   B3      50
9   B   B4      40
10  B   B5      68

Required output Top 3 record from “Table2” with Order by SecondFrequency Desc Ex.

Word    Frequency   SecondWord  SecondFrequency
A   1       A5      20
A   1       A3      10
A   1       A4      9   
B   5       B5      68
B   5       B3      50
B   5       B4      40

How can i get the desire output

I tried some Urls [link1](http://stackoverflow.com/questions/2043259/sql-server-how-to-join-to-first-row) [link1](http://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) but not getting success — Kaplesh, Mar 07 '17 at 06:41

score 2 · Answer 1 · answered Mar 07 '17 at 06:43

  Use ROWNUMBER function based on second frequency for get you required result:

 CREATE TABLE #Table1(Id  TINYINT, Word VARCHAR(1),Frequency TINYINT)
 CREATE TABLE #Table2(Id  TINYINT, Word VARCHAR(1),SecondWord 
 VARCHAR(2),SecondFrequency TINYINT)

 INSERT INTO #Table1(Id, Word ,Frequency)
 SELECT 1,'A',1 UNION ALL
 SELECT 2,'B',5

 INSERT INTO #Table2(Id, Word ,SecondWord ,SecondFrequency)
 SELECT 1,'A','A1',1 UNION ALL           
 SELECT 2,'A','A2',5 UNION ALL
 SELECT 3,'A','A3',10 UNION ALL 
 SELECT 4,'A','A4',9 UNION ALL
 SELECT 5,'A','A5',20 UNION ALL
 SELECT 6,'B','B1',5 UNION ALL 
 SELECT 7,'B','B2',8 UNION ALL
 SELECT 8,'B','B3',50 UNION ALL
 SELECT 9,'B','B4',40 UNION ALL
 SELECT 10,'B','B5',68

 SELECT *
 FROM 
 (
    SELECT ROW_NUMBER() OVER(PARTITION BY #Table1.Word ORDER BY  
            SecondFrequency DESC ) RNo ,#Table1.Word ,#Table1.Frequency,
            SecondWord ,SecondFrequency
    FROM #Table1
    JOIN #Table2 ON #Table1.Word = #Table2.Word
 ) A 
  WHERE RNo BETWEEN 1 AND 3

Mark · Accepted Answer · 2017-03-07T07:23:09.407

you can use Row Number. By using Row Number you can give each row with the same 'word' a number based on their SecondFrequency. those number will be reset if the 'word' is changed.

;with cte as
(
    select *, ROW_NUMBER() OVER (PARTITION BY Word ORDER BY SecondFrequency DESC) AS RowNumber from table2
)
select A.Word, B.Frequency, A.SecondWord, A.SecondFrequency 
    from cte A left join table1 B
       on A.Word = B.Word
where A.RowNumber < 4

score 0 · Answer 3 · answered Mar 07 '17 at 06:48

Inner Join with Row_Number() will help in this case !!!

CREATE TABLE #Table1
(
    Id  INT
    ,Word    VARCHAR(10)
    ,Frequency  INT
)

INSERT INTO #Table1 SELECT 1,'A',1
UNION SELECT 2,'B',5

CREATE TABLE #Table2
(
    Id  INT
    ,Word    VARCHAR(10)
    ,SecondWord  VARCHAR(10)
    ,SecondFrequency INT
)

INSERT INTO #Table2 SELECT
1,'A','A1',1 UNION ALL SELECT 
2,'A','A2',5 UNION ALL SELECT 
3,'A','A3',10 UNION ALL SELECT 
4,'A','A4',9 UNION ALL SELECT 
5,'A','A5',20 UNION ALL SELECT 
6,'B','B1',5 UNION ALL SELECT 
7,'B','B2',8 UNION ALL SELECT 
8,'B','B3',50 UNION ALL SELECT 
9,'B','B4',40 UNION ALL SELECT 
10,'B','B5',68

SELECT * FROM #Table1
SELECT * FROM #Table2

SELECT X.Word,X.Frequency,X.SecondWord,X.SecondFrequency
 FROM 
(SELECT T1.Word,T1.Frequency,T2.SecondWord,T2.SecondFrequency,ROW_NUMBER() OVER(PARTITION BY T1.WORD ORDER BY T2.SecondFrequency desc) as RN
FROM #Table1 T1
JOIN #Table2 T2
ON T1.Word = T2.Word
) AS X
WHERE X.RN<=3

score 0 · Answer 4 · answered Mar 07 '17 at 06:56

get the top 3 rows from Table_2
join the Table_1

the syntax is : ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) AS num COL1 is the column to group and COL2 is the column to sort , num is the sorted number to be used to limit the results

SELECT t2.Word,
       t1.Frequency,
       t2.SecondWord,
       t2.SecondFrequency
FROM
  (SELECT *
   FROM
     (SELECT Word,
             SecondWord,
             SecondFrequency,
             ROW_NUMBER() over(PARTITION BY Word
                               ORDER BY SecondFrequency DESC) AS num
      FROM Table_2) T
   WHERE T.num <= 3 ) t2
JOIN Table_1 AS t1 ON t2.Word = t1.Word
ORDER BY t2.SecondFrequency DESC;

Complex join in sql with top 10 row

4 Answers4