help with a query

hi guys. I am trying to write a query but dont know how to.

TABLES: Category (id,name ) ; SubCategory (id,Category_ID) , Article (id,SubCategory_ID,title)

I would like to display TOP 5 articles for each category.

GROUP BY is probably the answer...but I just don't know how to that...I hope you know. thanks.

If you are using SQL Server 2005:

Here is one solution:

SELECT t.Category_ID,t.Name, t.title, t.TOP5 FROM (SELECT Category$.Category_ID,Category$.Name, articles$.title, ROW_NUMBER() OVER (PARTITION BY Category$.Category_ID
ORDER BY Category$.Category_ID) AS TOP5
FROM SubCategory$ INNER JOIN
articles$ ON SubCategory$.SubCategory_ID = articles$.SubCategory_ID INNER JOIN
Category$ ON SubCategory$.Category_ID = Category$.Category_ID) t
WHERE t.TOP5<=5

|||

I tried this but i work on sqlserver express...any other option?

|||It should work on your SQL Server 2005 express database.|||

error I get is:The OVER SQL construct or statement is not supported.

maybe we can use a GROUP by that will make it something that the sqlsserver will understand?

|||

Here is another one:

SELECT

t3.Category_ID, t3.SubCategory_ID, t3.ID, t3.Name, t3.titleFROM(SELECT t1.Category_ID, t1.SubCategory_ID, t1.ID, t1.Name, t1.title,(SELECTCOUNT(*)FROM(SELECT Category$.Category_ID, SubCategory$.SubCategory_ID, articles$.ID, Category$.Name, articles$.titleFROM SubCategory$INNERJOIN articles$ON SubCategory$.SubCategory_ID= articles$.SubCategory_IDINNERJOIN Category$ON SubCategory$.Category_ID= Category$.Category_ID) t2WHERE t1.Category_ID=t2.Category_IDAND t2.ID<=t1.ID)as rankNum

FROM

(SELECT Category$.Category_ID, SubCategory$.SubCategory_ID, articles$.ID, Category$.Name, articles$.title

FROM

SubCategory$INNERJOIN

articles$

ON SubCategory$.SubCategory_ID= articles$.SubCategory_IDINNERJOIN

Category$

ON SubCategory$.Category_ID= Category$.Category_ID) t1) t3

WHERE

t3.rankNum<6|||

it's working :)

thank you guru :)

after this was done, I need to display it on ASP.NET html table.

each 5 records will create 1 table with 5 rows. I know there is a gridview but it takes care for only 1 record each time.

thanks alot for the query i would never come to this