Inner join two tables and list out specific rows-CodePudding

Let's say I have two tables as following:

 -------------- ------- 
| ID        | Score   |  
 -------------- -------
| 123       | 88      | 
| 456       | 77      |  
| 789       | 88      |     
| 111       | 77      |   
| 555       | 77      |   
|444        | 88      | 
| 222       | 77      | 
| 333       | 88      |

SECOND TABLE:

 -------------- ----- 
| ID      |NAME        | FScore   |  
 -------------- -------
|  123    |John        | 106      | 
|  456    |Aaron       | 99       | 
|  789    |Dan         | 105      |    
|  111    |Kevin       | 200      |   
|  555    |Tom         | 100      |  
|  444    |Jeff        | 120      | 
|  222    |Carl        | 65       | 
|  333    |Wayne       | 101      |

I want to join two tables and based on FScore find out top 3 88 and top 3 77 rows like:

 -------------- -------------- ------ ----- 
| ID        | Score   | NAME    | FScore | 
 -------------- -------------- ------ ----- 
| 444       | 88      |  Jeff  | 120 | 
| 123       | 88      |  John  |  106   | 
| 789       | 88      |  Dan  |  105   |

Any helps are appreciated!

CodePudding user response：

You can use a CTE that returns the joined tables and then filter the resultset with a correlated subquery:

WITH cte AS (
  SELECT *
  FROM table1 t1 INNER JOIN table2 t2
  ON t2.ID = t1.ID
  WHERE t1.Score IN ('77', '88')
)
SELECT c1.ID, c1.Score, c1.Name, c1.FScore
FROM cte c1
WHERE (SELECT COUNT(*) FROM cte c2 WHERE c2.Score = c1.Score AND c2.FScore >= c1.FScore) <= 3;

Or, with ROW_NUMBER() window function:

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY t1.Score ORDER BY t2.FScore DESC) rn
  FROM table1 t1 INNER JOIN table2 t2
  ON t2.ID = t1.ID
  WHERE t1.Score IN ('77', '88')
)
SELECT ID, Score, Name, FScore
FROM cte
WHERE rn <= 3;

See the demo.