how to select top n rows from each group id in sql server?

i want to select top 5 rows from my table for each group id my table is

CREATE TABLE [dbo].[news](
    [pk_news] [int] IDENTITY(1,1) NOT NULL,
    [text] [text] NULL,
    [title] [nvarchar](500) NULL,
    [pk_service] [int] NULL,
    [image] [nvarchar](500) NULL,
    [pk_annalist] [int] NULL,
    [pk_user] [int] NULL,
    [pk_admin] [int] NULL,
    [accept] [int] NULL,
    [views] [int] NULL,
    [tag] [nvarchar](500) NULL,
    [news_Date] [date] NULL,
    [summary] [nvarchar](500) NULL,

select 
    x.*
    ,n.*
from dbo.news n
join (
    select 
        pk_news
        ,[rwn] = row_number() over(partition by pk_service order by pk_news asc)
    from dbo.news
) x
on n.pk_news=x.pk_news and x.rwn < 6
order by 
    n.pk_service, x.rwn



About Author: author 4 + years of Information Technology experience in understanding and analyzing the software and hardware engineering requirements, onsite and offshore management of product development. Expert work on design and development for windows,web,mobile Expert knowledge of C#,ADO.NET, ASP.Net MVC, ENTITY FRAMEWORK, LINQ,COLLECTIONS, JAVASCRIPT,AngularJs 1.0,Angular2, Jquery,Jquery-Ajax, SQL SERVER, XAML,Windows phone 8.1 Good knowledge of WCF-Restful services,WCF, WEB API-Services ,Web- Services,MIcrosoft-Azure,JUICE UI,HTML,CSS,AJAX Read More...

Join him on Google+ | Facebook | Linkedin

No comments