How to select no more than N records for a given column value in a table?

Question

I have a table called NewsArticles that has column identifiers, LanguageID, Date, Title, ArticleContent. I want to create a presentation that selects the 5 most recent news articles in each language in the database.

For example, if I have 3 languages, say, English, French and German, the query should return 15 records containing the 5 latest news articles in England, 5 ... you get a picture. How can I build such a query?

For each unique LanguageID in NewsArticles, return 5 entries, sorted by date in descending order.

Answer 1

It is quite simple with CTE.

;with x as 
(
    select ID, LanguageID, Title, Date,
            row_number() over ( partition by LanguageID order by Date DESC  ) as position
    from NewsArticles
)
select * from x 
where Position < 6

Answer 2

CREATE VIEW Top5Articles
AS     
    SELECT top 5 * FROM LatestNews WHERE LanguageID = 1 order by createdontime desc // latest 5 English news articles
    union
    SELECT top 5 * FROM LatestNews WHERE LanguageID = 2 order by createdontime desc
    union
    SELECT top 5 * FROM LatestNews WHERE LanguageID = 3 order by createdontime desc

, createdontime , .

.

Answer 3

Do you want something like this -

select *
from LatestNews
where (
   select count(*) from LatestNews as A
   where A.LanguageID = LatestNews.LanguageID 
) <= 5 order by Date DESC;