Easiest way to limit JOIN to only select TOP 1

Question

I am using the following query for the main level of a procedure with two nested queries.

The query works and selects the correct data, my only issue is that I would only need the TOP 1 record from the JOIN based on an ORDER BY modTime desc.

I found a few posts on this on Google with very different approaches and was not sure which one is the right one here. Example source: How do I limit a LEFT JOIN to the 1st result in SQL Server?

I like the way of wrapping an Inner Join in the Left Join but wasn't sure if that makes sense and how to apply it here. What would be the easiest approach and how to apply this here?

My SQL:

ALTER PROCEDURE [dbo].[MOC_FetchMenu]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT      A.itemID,
                COALESCE(A.parentID, '999999'),
                A.itemName,
                A.linkRef,
                A.sortID,
                COUNT(*) OVER() AS volume,
                D.lastUpdate,
                CONVERT(VARCHAR(11), D.modTime, 106) AS modTime,
                D.modBy,
                '---' AS levelSub,
                (
                  -- begin of my nested queries
                )
    FROM        MOC_Links A
    LEFT JOIN   MOC_Log D
    ON          D.itemID = A.itemID
    WHERE       A.parentID = '1'
    ORDER BY    A.itemName
    FOR XML PATH('level1'), ELEMENTS, TYPE, ROOT('ranks')

END

Thanks. I tried that but then got the error I can only have one subquery. How would that work with my other nested queries ? — user2571510, Aug 20 '14 at 08:19
A subquery with top 1 won't work, he wants 1 row returned per JOIN, not just 1 row. — NickyvV, Aug 20 '14 at 08:21
What is the table you are trying to get the TOP 1 out of? MOC_Links, MOC_Log, or a totally different table? If it's a different table what is the join column? — Nick.Mc, Aug 20 '14 at 08:22

score 2 · Accepted Answer · edited Aug 20 '14 at 08:30

2

Using Cross Apply or Outer Apply

SELECT  * 
FROM    MOC_Links A
        OUTER APPLY
        (SELECT TOP 1 *
         FROM   MOC_Log AS D
         WHERE  D.itemID = A.itemID
         ORDER BY D.modTime DESC) D

edited Aug 20 '14 at 08:30

NickyvV

1,720
2
16
18

answered Aug 20 '14 at 08:22

mordechai

829
1
7
23

Thanks a lot - this works great ! Will accept as soon as I can. :) – user2571510 Aug 20 '14 at 08:25

score 0 · Answer 2 · answered Aug 20 '14 at 08:25

Try This,

select top A.itemID,
COALESCE(A.parentID, '999999'),
A.itemName,
A.linkRef,
A.sortID,
COUNT(*) OVER() AS volume,
D.lastUpdate,
CONVERT(VARCHAR(11), D.modTime, 106) AS modTime,
D.modBy,
'---' AS levelSub,(select top 1 ColumnName from Table)  FROM        MOC_Links A
LEFT JOIN   MOC_Log D
ON          D.itemID = A.itemID
WHERE       A.parentID = '1'
ORDER BY    A.itemName
FOR XML PATH('level1'), ELEMENTS, TYPE, ROOT('ranks')

Easiest way to limit JOIN to only select TOP 1

2 Answers2

Linked