October 20, 2009 at 12:22 pm
I've been working on this problem for like an hour now, and I know this should be a very simple problem to solve, but I can't seem to wrap my head around it.
Let's say I have a table like this:
CREATE TABLE #Temp
(
ID INT PRIMARY KEY IDENTITY,
[Date] SMALLDATETIME,
SubjectLine VARCHAR(MAX),
[Content] VARCHAR(MAX),
CategoryID INT
)
INSERT INTO #Temp([Date], SubjectLine, [Content], CategoryID)
VALUES('2009-01-01', 'Test1', 'TestContent', 1)
INSERT INTO #Temp([Date], SubjectLine, [Content], CategoryID)
VALUES('2009-01-01', 'Test1', 'TestContent2', 2)
INSERT INTO #Temp([Date], SubjectLine, [Content], CategoryID)
VALUES('2009-01-01', 'Test1', 'TestContent3', 3)
INSERT INTO #Temp([Date], SubjectLine, [Content], CategoryID)
VALUES('2009-02-01', 'Test2', 'TestContent4', 1)
INSERT INTO #Temp([Date], SubjectLine, [Content], CategoryID)
VALUES('2009-02-01', 'Test2', 'TestContent5', 3)
INSERT INTO #Temp([Date], SubjectLine, [Content], CategoryID)
VALUES('2009-03-01', 'Test3', 'TestContent6', 2)
INSERT INTO #Temp([Date], SubjectLine, [Content], CategoryID)
VALUES('2009-03-01', 'Test3', 'TestContent7', 3)
INSERT INTO #Temp([Date], SubjectLine, [Content], CategoryID)
VALUES('2009-04-01', 'Test4', 'TestContent8', 3)
Now, if I do a query like this:
SELECT [Date], SubjectLine
FROM #Temp
GROUP BY [Date], SubjectLine
Then I get a recordset like this, which I expect to get:
DateSubjectLine
2009-01-01 00:00:00Test1
2009-02-01 00:00:00Test2
2009-03-01 00:00:00Test3
2009-04-01 00:00:00Test4
What I want, is to have a query where I have an extra field showing in the recordset. The content of this field should be the [Content] field, IF that particular date grouping contains a record with CategoryID = 1. If it does not contain a record with categoryID = 1 then the date field should be an empty field. What the resultset should look like is this:
DateSubjectLineContent
2009-01-01 00:00:00Test1TestContent
2009-02-01 00:00:00Test2TestContent4
2009-03-01 00:00:00Test3
2009-04-01 00:00:00Test4
But I can't seem to find a "clean" solution for this. The only thing I can think of involves several subqueries, which I'd rather avoid if possible.
October 20, 2009 at 12:35 pm
Will this do?
SELECT [Date], SubjectLine, max(case when categoryID = 1 THen CONTENT END) as Content
FROM #Temp
GROUP BY [Date], SubjectLine
---------------------------------------------------------------------------------
October 20, 2009 at 12:40 pm
Sweet. That's a cute solution, though I'm somewhat surprised that it works!
What does MAX() do when it encounters a VARCHAR field? Like, how does it evaluate between two different sets of VARCHAR values and determine which one is the MAX? IE, if there were two records which both had CategoryID = 1, how would it know which to pick? The one with the max length?
October 20, 2009 at 12:51 pm
Please look at the explanation by Seth below. Thanks.
---------------------------------------------------------------------------------
October 20, 2009 at 1:07 pm
IIRC, varchar values are compared by looking at the ANSI values of each character going from left to right.
DECLARE @a TABLE(vc varchar(20))
INSERT INTO @a(vc)
SELECT 'bafeajk' UNION ALL
SELECT 'abfaefafea' UNION ALL
SELECT 'z' UNION ALL
SELECT 'zzz' UNION ALL
SELECT 'zyzzzzz'
SELECT * FROM @a
ORDER BY vc desc
October 21, 2009 at 12:56 am
oh thanks Seth, I was not completely sure about that. I had to use an aggregate function there and had chosen max.
@kramaswamy, sorry I was not 100% sure bout my explanation there. Please go with Seth. Thanks.
---------------------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply