SQL Server 2008 select top 1 row from multiple tables?

I have a table that stores articles, like the example:

Article table:

ID #CategoryID #Text #Date

So on the page I have different sections and each section has its own categoryID. For example, sports is 1, news is 2 etc.

Now I want the possibility开发者_开发技巧 to load the latest article from x number of categories. I have an SP that takes an nvarchar with the ID's separated by space.

So now the question is, how can I select the latest inserted article from the categories in the nvarchar?

I use Erland Sommerskog's nvarchar-to-table to get the ID's into a table (http://www.sommarskog.se/arrays-in-sql-2005.html#iter-list-of-integers)

Normally I would use something like this to select articles from several categories:

SELECT TOP 5 ArticleID, Headline, CategoryID, ShortDescription, DatePublished 
FROM Article 
WHERE ArticleState = 3 
AND CategoryID IN (SELECT i.number FROM iter_intlist_to_tbl(@Categories) AS i)
ORDER BY DatePublished DESC

But how do I only select the last article from each of the categories supplied?

SELECT  a.*
FROM    iter_intlist_to_tbl(@Categories) i
OUTER APPLY 
        (
        SELECT  TOP 1 *
        FROM    Article
        WHERE   CategoryID = i.number
                AND ArticleState = 3
        ORDER BY
                DatePublished DESC
        ) a

This will select latest article with ArticleState = 3 from each category, or a NULL if there are no such articles in a category.

I know this question has been answered but you could rank the various categories and just use WHERE clause to filter them with OR like WHERE rankA =1 OR rankB = 1 OR rankc = 1

it will print all top ranks for example, I have ranked Salary and Costs in this query and have printed

WITH RESULT AS
(
    SELECT M.UserID
        ,M.FirstName
        ,M.LastName
        ,M.Salary
        ,Salary_Rank = DENSE_RANK() OVER(ORDER BY Salary DESC)
        ,M.Costs
        ,Costs_Rank = DENSE_RANK() OVER(ORDER BY Costs DESC)
    FROM temp.dbo.MainData AS M
)
SELECT R.UserID
    ,R.FirstName
    ,R.LastName
    ,R.Salary
    ,R.Salary_Rank
    ,R.Costs
    ,R.Costs_Rank

FROM RESULT AS R
WHERE Salary_Rank = 1 OR Costs_Rank = 1

Sample data