select latest 2 records of each product id in single table

Question

this is my table structure,

create table ArticleTbl
(
ArticleID bigint identity(1,1),
ProductID int ,
ArticleName varchar(100),
PubDate datetime,
AuthorName varchar(50),
AuthorImage bit,
HtmlValues nvarchar(max)
)

here productid are

1=creditcard,2=prepaidcard,3 saving account,.........

each productid is having multiple rows of records , i want to select latest 2 records of each productid in one shot instead of going to database each time .

my procedure now is like..

create proc USP_GetArticle_ByProduct(@ProductID int) as 
select top(2) * from ArticleTbl where ProductID=@ProductID

if i use this procedure each productid i have to go to database...

how to get one shot all product(latest 2 records ) using query????

Answer 1

SELECT
    *
FROM
    (
    SELECT
        /*Random order per product*/
        ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY NEWID() ) AS Ranking,
        *
    FROM
        ArticleTbl
    ) foo
WHERE
   foo.Ranking <= 2

Answer 2

i figure this is on sql server yeah?

if so, you could do this...

select  a1.*
from Articletbl a1
where a1.articleid in
    (select top 2 a2.articleid 
    from ArticleTbl a2
    where a2.productid = a1.productid
    order by a2.articleid DESC)
order by a1.ProductID