SQL Server 2008 select top 1 row from multiple tables?

Question

Hello!

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?

Answer 1

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.