Link to home
Start Free TrialLog in
Avatar of cabou
cabou

asked on

5 records from each categories

Hi experts,

I have a table [Article] like this

ArticleID, Title, Category

What is the best way to get the 5 first records from category 2,3 and 5.

Thanks
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

what do you call "first" records?

let me assume, lowest articleID
select t.*
from article t
where t.ArticleID IN ( Select TOP 5 i.ArticleID from article i where i.Category = t.Category order by i.ArticleID ASC )

Open in new window

This can probably be done in multiple ways, but anyway you need to decide "top 5 by what"? sql queries usually returns results in random order. Also: do you want to get the top 5 from category 2, then top 5 from category 3, the top 5 from category 5, or do you want to get the top 5 from the combination of all categories?


Looking at just 1 category, you can probably do something like this:

Oracle 8i and above:
SELECT *
  FROM (SELECT * FROM Article ORDER BY col_name_1 DESC where category=[id])
 WHERE ROWNUM < 5;

MS SQL:
SELECT TOP 5 title,ArticleID
FROM Article
WHERE category=2
ORDER BY ArticleID

Avatar of cabou
cabou

ASKER

angelIII

I do not understand where to write category id 2,3 and 5
sorry, forgot that:
select t.*
from article t
where t.Category in ( 2,3,5 )
and t.ArticleID IN ( Select TOP 5 i.ArticleID from article i where i.Category = t.Category order by i.ArticleID ASC )

Open in new window

Avatar of cabou

ASKER

Angel,

Sorry,  in fact my table is like that. I do not understand how to do it.

SELECT top 5    DnnForge_NewsArticles_Article.*, DnnForge_NewsArticles_ArticleCategories.CategoryID
FROM         DnnForge_NewsArticles_ArticleCategories INNER JOIN
                      DnnForge_NewsArticles_Article ON DnnForge_NewsArticles_ArticleCategories.ArticleID = DnnForge_NewsArticles_Article.ArticleID
order by DnnForge_NewsArticles_Article.articleid desc
let's see:
select t.*, tc.CategoryID
from DnnForge_NewsArticles_Article t
join DnnForge_NewsArticles_ArticleCategories tc
  on tc.articleID = t.ArticelID
 and tc.CategoryID in ( 2,3,5 )
where t.ArticleID IN ( Select TOP 5 i.ArticleID 
                      from DnnForge_NewsArticles_ArticleCategories i 
                       where i.CategoryID= t.CategoryID  
                       order by i.ArticleID ASC 
                   )

Open in new window

Avatar of cabou

ASKER

Here is what i get

Msg 207, Niveau 16, État 1, Ligne 4
Nom de colonne non valide : 'ArticelID'.
Msg 207, Niveau 16, État 1, Ligne 8
Nom de colonne non valide : 'CategoryID'.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial