Grouping and ordering by a category

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)

Grouping and ordering by a category

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2009-04-13 : 08:55:51

I have two tables:

Articles
ArticleId
CategoryID
Title
ReleaseDt

Category
CategoryID
CategoryName

I want a list that displays all articles written by person let us say 'Jacob' grouped by Category and in between releasedt '1/1/2009' to 'current date'

Sample Result

ArticleID  	CategoryName	Title	 	 ReleaseDt
23		CategoryA	ArticleA	 3/2/2009
25		CategoryA	ArticleB	 3/6/2009
13		CategoryB	ArticleC	 2/4/2009
56		CategoryB	ArticleD	 2/9/2009

Sample Data

DECLARE @Articles table
(
ArticleId int,
CategoryID int
Title varchar(30)
ReleaseDt datetime

)

INSERT @Articles 
SELECT 23,22,'ArticleA' UNION ALL 
SELECT 24,25,'ArticleE' UNION ALL
SELECT 25,22,'ArticleB' UNION ALL
SELECT 13,25,'ArticleC' UNION ALL
SELECT 56,25,'ArticleD' 


DECLARE @Category table
(
 Categoryid int, CategoryName varchar(30)
)

INSERT @Category
SELECT 22,'CategoryA' UNION ALL
SELECT 25,'CategoryB'

Thanks in advance.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-13 : 09:01:23

declare @str datetime
Select @str = '1/1/2009'
select articleid,categoryname,title,releasedt
from articles a
inner join category c on c.categoryid = a.categoryid
where dateadd(d,datediff(d,0,releasedt),0) between @str and dateadd(d,datediff(d,0,getdate()),0)
order by categoryname

lols
Posting Yak Master

174 Posts

Posted - 2009-04-13 : 09:03:32

Thanks but it does not give expected results as per the Output i showed. I want the Categories to be grouped together and ordered by date. I think a where clause also needs to come to filter the author?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-13 : 09:10:45

can u post some sample data ........ for these tables

lols
Posting Yak Master

174 Posts

Posted - 2009-04-13 : 09:22:04

Updated the sample data in the question

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources