SQL Server 2008 picking the first row from multiple tables?
I have a table that stores articles, for example an example:
Articles table:
ID #CategoryID #Text #Date
So, on the page I have different sections and each section has its own category ID. For example, sport is 1, news is 2, etc.
Now I want to download the last article from the number x. I have a SP that accepts nvarchar with a space separated ID.
So now the question is, how can I select the last inserted article from categories in nvarchar?
I am using Erland Sommerskog nvarchar-to-table to get the id into the table ( http://www.sommarskog.se/arrays-in-sql-2005.html#iter-list-of-integers )
Typically 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 can I select only the latest article from each of the categories provided?
source to share
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 the last article ArticleState = 3
from each category, or NULL
if there are no such articles in the category.
source to share