SP Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SP Help

Author

Topic

Dejan
Starting Member

14 Posts

Posted - 2008-01-12 : 14:27:00

hello,
I'm new here.
I have 10 categories in my web page (in database). I'd like to publish first 5 articles from each category and publish them on front page. What is the best practice to do that?
I should get this:

What I have to do? To create stored procedures for all categories, or create one stored procedure all categories.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-01-12 : 19:21:06

What you have to do? Maybe post some sample data so we don't have to make it?

--Jeff Moden

Dejan
Starting Member

14 Posts

Posted - 2008-01-12 : 20:04:52

How to write stored procedure to display data showen on the picture...

quote:
Originally posted by Jeff Moden

What you have to do? Maybe post some sample data so we don't have to make it?

--Jeff Moden

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-13 : 00:57:10

You can do it by single stored procedure. use code below:-

SELECT t.Category,t.Article
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Category ORDER BY Article DESC) AS RowNo,
Category
Article
FROM Table)t
WHERE t.RowNo<=5
ORDER BY t.Category

This will give you first five article for each category in alphabetical order(if you want it in reverse alphabetical use code in blue also).
the o/p will be of the form

Category  Article
Category1  Article1
Category1  Article2
Category1  Article3
Category1  Article4
Category1  Article5
Category2  Article1
Category2  Article2
Category2  Article3
........
Category10  Article5

Dejan
Starting Member

14 Posts

Posted - 2008-01-13 : 08:37:01

I can't get results in DataList control....

quote:
Originally posted by visakh16

You can do it by single stored procedure. use code below:-

SELECT t.Category,t.Article
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Category ORDER BY Article DESC) AS RowNo,
Category
Article
FROM Table)t
WHERE t.RowNo<=5
ORDER BY t.Category

This will give you first five article for each category in alphabetical order(if you want it in reverse alphabetical use code in blue also).
the o/p will be of the form

Category Article
Category1 Article1
Category1 Article2
Category1 Article3
Category1 Article4
Category1 Article5
Category2 Article1
Category2 Article2
Category2 Article3
........
Category10 Article5

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-13 : 11:28:56

quote:
Originally posted by Dejan

I can't get results in DataList control....
quote:
Originally posted by visakh16

You can do it by single stored procedure. use code below:-

SELECT t.Category,t.Article
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Category ORDER BY Article DESC) AS RowNo,
Category
Article
FROM Table)t
WHERE t.RowNo<=5
ORDER BY t.Category

This will give you first five article for each category in alphabetical order(if you want it in reverse alphabetical use code in blue also).
the o/p will be of the form

Category Article
Category1 Article1
Category1 Article2
Category1 Article3
Category1 Article4
Category1 Article5
Category2 Article1
Category2 Article2
Category2 Article3
........
Category10 Article5

where? query analyser or ur application? if its application,then cant you split this o/p to be shown categorywise?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources