how to select top n rows from each group id in same table

Question

i wana too 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,

and i want too select rows from each pk_service

i google it and find a soloution by cross apply but this way get too long time from my prossesor — afzali, Mar 14 '13 at 13:17
you don't need to use `cross apply`, just `partition by`, see the answer in the link from @gbn comment — Dis Shishkov, Mar 14 '13 at 13:21
If the cross apply solution was slow then probably you have an index problem. A version using windowed functions (partition by) won't be much faster .. — Dumitrescu Bogdan, Mar 14 '13 at 13:33

Charles Bretana · Answer 1 · 2013-03-14T13:35:46.303

First you have to specify what it means to be in the "top 5" Is this by Number of views?, latest news_date?, what? assuming it is by number of views, then:

First, you You need an expression that calculates, for each row, how many records there are in the same pk_select that have a value of views greater than the current records value.

Assuming n is alias for the current row, That would be

    Select Count(*) From news 
    Where pk_select = n.pk_Select
        And views >= n.views

Then embed this as a subselect in the Where clause of an an outer query

 Select * From news n
 Where (Select Count(*) From news 
        Where pk_select = n.pk_Select
           And views >= n.views) < 5

This generates the absolutely correct answer only if the values of views do not include duplicates in each pk_Select group. If there are duplicates, then you need to use row_number function

   With Rows As
   (
          Select *, 
          Row_Number() Over (Order By Views) as rowNum
          From news
   )
   Select * From Rows
   Where RowNum <= 5

score 2 · Accepted Answer · answered Mar 14 '13 at 13:31

2

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

answered Mar 14 '13 at 13:31

AdamL

12,421
5
50
74

how to select top n rows from each group id in same table

2 Answers2

Linked