TSQL - TOP X in FROM Subquery?

Question

Can someone please enlighten me to a way to filter a subquery that is located in a FROM clause? I would like it to look something like this:

SELECT *
FROM TABLE_A
LEFT JOIN (TOP 8 TABLE_B) ON TABLE_B.id = TABLE_A.id

Solution 1

Please try this:

SELECT 
column_names 
FROM 
TABLE_A A LEFT JOIN (SELECT TOP 8 column_names FROM TABLE_B) as B
on A.Id=B.ID

Considerations:

Do not use * since it would lead to performance constraints.

IF you are concerned about just the ID then get only the ID from Table_B

HTH

Solution 2

If you need to correlate the subquery then you need to use APPLY instead of JOIN:

SELECT *
FROM TABLE_A
CROSS APPLY (
 SELECT TOP (8) *
 FROM TABLE_B
 WHERE TABLE_B.id = TABLE_A.id
 ORDER BY ...) AS B;

This will give you the top 8 rows from B for each row in A. The other solutions I see posted will give you the JOIN between A and the global TOP 8 from B

Solution 3

SELECT * 
FROM TABLE_A AS a
LEFT JOIN (SELECT TOP 8 id, field1, field2
           FROM TABLE_b) AS b
    ON a.id = b.id

Should work.

Solution 4

SELECT *
FROM TableA
LEFT JOIN ( SELECT TOP 8 * FROM TableB) B
   ON B.id=TableA.id

Solution 5

You might consider a different approach such as:

SELECT * FROM TABLE_A WHERE TABLE_A.ID IN ( SELECT TOP 8 ID FROM TABLE_B )

View more solutions