GROUP BY error - 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

General SQL Server Forums

New to SQL Server Programming

GROUP BY error

Author

Topic

oap
Yak Posting Veteran

60 Posts

Posted - 2012-02-28 : 18:13:58

Hi

I have a basic table consisting of the following columns:

content (plain text)
name (name of a location example: Toronto)
stamp (datetime field for the datestamp)
locid (integer value assigned to a location)
parent (will be a numeric value)

The table is used for storing comments. People enter their comment and it gets stored in the content field, posted under the city ("name" field) which has a location ID (locid) and timestamped ("STAMP" field)

This is how I read the comments:

strSQL = "SELECT * FROM commenttable WHERE locid = " & val & " ORDER by stamp desc"

Newest comments are displayed first.

I am trying to implement nested replies so if you reply to a particular comment, it will appear below the comment you replied to rather than at the top of the comments as the newest.

For this I created the 'parent' field. The fields are currently NULL but if a new reply is made, I plan to give the reply a value that is also assigned to the original reply.

Example:
"This is a reply" (parent:1)
"This is another reply" (parent: 2)
"Did you visit Toronto yet?" (parent: 3)
"Yes I did, last week: (parent: 3)

Anyway when I went to group the comments
strSQL = "SELECT * FROM commenttable WHERE locid = " & val & " GROUP BY parent ORDER by stamp desc"

I received an error about the column must be contained in an aggregate function. What I want to do is very simple, retrieve the newest to oldest fields sorted by 'stamp' (timedate) and grouped by 'parent'.

When I say grouped, I mean that all comments hacving the same parent value (as in parent/child nesting) will be displayed together.

Help?? :)

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-28 : 18:29:45

Do you have SQL Server Client tools installed?

Do you know what stored procedures are

You are going to need to use Common Table Expressions (CTE) for hierarchical queries

Who is your DBA?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

oap
Yak Posting Veteran

60 Posts

Posted - 2012-02-28 : 19:13:53

There is no DBA, only an ASP coder (me).

I use SQL Server Management Studio for all the back end work and the site is hosted remotely.

It sounds rather complicated for wanting to group records together by a given label.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-28 : 19:23:43

use a CTE like below


;WITH Comments
AS
(
SELECT id,content,stamp,1 as level,CAST(NULL AS datetime) AS parentdate,CAST(NULL AS integer) AS parentid
FROM commenttable 
WHERE parent IS NULL
AND locid = @val

UNION ALL

SELECT t.id,t.content,t.stamp,c.level + 1 ,c.stamp,c.id
FROM commenttable t
INNER JOIN Comments c
ON c.id = t.parent 
)

SELECT *
FROM Comments
ORDER BY COALESCE(parentdate,stamp) DESC,COALESCE(parentid,id),Level

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

oap
Yak Posting Veteran

60 Posts

Posted - 2012-02-28 : 19:28:03

After some reading, I understand that ORDER BY is for operstions such as SUM, MIN, etc. and the task wasn't as easy as I thought. :)

Thank you for the code...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-28 : 19:40:07

quote:
Originally posted by oap

After some reading, I understand that ORDER BY is for operstions such as SUM, MIN, etc. and the task wasn't as easy as I thought. :)

Thank you for the code...

Hm...
Where did you read that?
there's nothing like that
ORDER BY is used for sorting the results and can be used even without aggregate functions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources