How to select a single record in a left join

Question

I need to select a specific model from the Models table using its key ModelID. I also need to add a blurb of content from the Model_Content table. The Models_Content table, however, has several blurbs of content for each model. I need to select just the first blurb.

My tables look like this:

 Models // table  ModelID // pk  Model // varchar   Models_Content // table  ContentID // pk  ModelID // fk  Content // varchar   SELECT M.ModelID, M.Model, C.Content  FROM   Models M LEFT JOIN Models_Content C ON M.ModelID =  C.ModelID  WHERE      M.ModelID = 5 

How do I adjust my query to select just the very first blurb of content for a specific model?

Answer 1

 SELECT    M.ModelID, M.Model, C.Content  FROM    Models M  LEFT JOIN    Models_Content C      ON C.ContentID = (SELECT MIN(ContentID) FROM Models_Content WHERE ModelID = M.ModelID)  WHERE    M.ModelID = 5 

Or

;WITH sorted_content AS (   SELECT     ROW_NUMBER() OVER (PARTITION BY ModelID ORDER BY ContentID) AS itemID,     *   FROM     Models_Content )  SELECT    M.ModelID, M.Model, C.Content  FROM    Models M  LEFT JOIN    sorted_content C      ON  C.ModelID = M.ModelID      AND C.itemID  = 1  WHERE    M.ModelID = 5 

Answer 2

Sean's answer is the best specific solution but just to add another "generalised" solution

SELECT M.ModelID,        M.Model,        C.Content FROM   Models M        OUTER APPLY (SELECT TOP 1 *                     FROM   Models_Content C                     WHERE  M.ModelID = C.ModelID                     ORDER  BY C.ContentID ASC) C WHERE  M.ModelID = 5