0

I have tables:

Category:  Id, Name...
News:   Id, Title

News_Category_Mapping:  Id, NewsId, CategoryId

Where newsid, categoryid are foreign keys to these 2 tables.

News_category_mapping:

Id  NewsID CategoryId
1     1        1
2     2        1
3     3        1
4     4        3 
5     5        5
6     6        3

so i may want to get maximum 2 news items from every categoryid, say like 

Id  NewsID CategoryId
1     1        1
2     2        1
4     5        3
6     6        3
5     5        5

Sorry for my english.

Yuriy Galanter
  • 38,833
  • 15
  • 69
  • 136
nam vo
  • 3,271
  • 12
  • 49
  • 76
  • 1
    any 2? or ordered by some crtieria? what version of SQL Server? If it's 2008, use the `RANK()` function. – Nick.Mc May 09 '14 at 10:41
  • care to give more information on the tables, i.e. all the columns and their data types, something like a desired output and what you have tried so far – Brett Schneider May 09 '14 at 10:54

3 Answers3

1

Try this:

WITH CTE AS
(SELECT C.Id,N.Id,N.Title,RN=ROW_NUMBER() OVER (PARTITION BY NC.CategoryID ORDER BY NC.NewsId)
 FROM News_Category_Mapping NC JOIN
     News N ON NC.NewsId=N.Id JOIN
     Category C ON NC.CategoryId=C.Id)
SELECT * FROM CTE WHERE RN<3

Explanation:

Here, the inner query selects the records along a row number RN. To know how the query works, please execute the inner query first.

Raging Bull
  • 18,593
  • 13
  • 50
  • 55
  • 1
    http://stackoverflow.com/questions/11169550/is-there-a-speed-difference-between-cte-subquery-and-temp-tables – podiluska May 09 '14 at 11:59
1

Let say you need 2 items each

Select *
From Category C
    CROSS APPLY (Select top 2  Id,CatId,NewsName  
                 From News Nw where Nw.CatId=C.Id) As N

Here is the fiddle sample

huMpty duMpty
  • 14,346
  • 14
  • 60
  • 99
0

You can use CROSS APPLY, like so:

Select c.*, Sub.*
from
  Categories c cross apply
  (
    select top 2 
      * 
    from 
      News n 
    where 
    exists
    (
       select 1 
       from NewsCategories nc 
       where nc.CatId = c.id and n.id = nc.NewsId
    )
  ) Sub

Here is an SQLFiddle for this

Tamim Al Manaseer
  • 3,554
  • 3
  • 24
  • 33