one row for each discrete timestamp. You should instead use an aggregate
function, like MAX(), to collapse all those rows down to a single value,
and then remove the MessageDate field from the GROUP BY clause.
_____________________
Eric A. Laney
_____________________
There is something in the pang of change, more than the heart can bear.
Unhappiness remembering happiness. -Euripides
"Andrew Scott" <[EMAIL PROTECTED]>
03/19/2004 01:13 AM
Please respond to sql
To: SQL <[EMAIL PROTECTED]>
cc:
Subject: RE: Can someone help me with this query
Brian,
If I did that it would only return the one record, I guess I need to do a
subquery.
Your idea return this
1 Category1 1 19/03/2004 4:12:00 pm
I need
1 Category1 2 19/03/2004 4:12:00 pm
2 Category2 1 null
3 Category3 0 null
4 Category4 0 null
Regards
Andrew Scott
Technical Consultant
NuSphere Pty Ltd
Level 2/33 Bank Street
South Melbourne, Victoria, 3205
Phone: 03 9686 0485 - Fax: 03 9699 7976
_____
From: Brian Ferrigno [mailto:[EMAIL PROTECTED]
Sent: Friday, 19 March 2004 5:03 PM
To: SQL
Subject: Re: Can someone help me with this query
Hi Andrew,
What DB are you using? If you're using SQL Server you
can use the TOP function.
SELECT TOP 1 nfCategory.nfCategoryId,
nfCategory.Name,
nfCategory.Description,
COUNT(nfCategory.nfCategoryId) AS TopicCount,
nfForumMessage.MessageDate
FROM nfForumMessage INNER JOIN
nfForumTopic ON nfForumMessage.nfForumTopicId =
nfForumTopic.nfForumTopicId RIGHT OUTER JOIN
nfCategory ON nfForumTopic.nfCategoryId =
nfCategory.nfCategoryId
GROUP BY nfCategory.nfCategoryId, nfCategory.Name,
nfCategory.Description,
nfForumMessage.MessageDate
ORDER BY nfForumMessage.MessageDate DESC
Hope this helps.
Brian
--- Andrew Scott <[EMAIL PROTECTED]> wrote:
> I want to return the top 1 field on this query so
> that the MessageDate
> returns 1 record sorted by desc to created a single
> query
>
> SELECT nfCategory.nfCategoryId, nfCategory.Name,
> nfCategory.Description,
> COUNT(nfCategory.nfCategoryId) AS TopicCount,
> nfForumMessage.MessageDate
> FROM nfForumMessage INNER JOIN
> nfForumTopic ON
> nfForumMessage.nfForumTopicId =
> nfForumTopic.nfForumTopicId RIGHT OUTER JOIN
> nfCategory ON
> nfForumTopic.nfCategoryId =
> nfCategory.nfCategoryId
> GROUP BY nfCategory.nfCategoryId, nfCategory.Name,
> nfCategory.Description,
> nfForumMessage.MessageDate
> ORDER BY nfForumMessage.MessageDate DESC
>
> The above returns
>
> 1 Category1 1 19/03/2004 4:10:00 pm
> 1 Category1 1 19/03/2004 4:12:00 pm
> 2 Category2 1 null
> 3 Category3 1 null
> 4 Category4 1 null
>
> When it should return this
>
> 1 Category1 2 19/03/2004 4:12:00 pm
> 2 Category2 1 null
> 3 Category3 1 null
> 4 Category4 1 null
>
>
>
> Regards
> Andrew Scott
> Technical Consultant
>
> NuSphere Pty Ltd
> Level 2/33 Bank Street
> South Melbourne, Victoria, 3205
>
> Phone: 03 9686 0485 - Fax: 03 9699 7976
>
>
>
>
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
