NickName:Thomas R Ask DateTime:2009-07-14T06:27:28

SQL Server GROUP BY error

Here is the query I am trying to run:

  SELECT TOP 5 PageComment.ID
    FROM PageComment
   WHERE PageComment.ParentID IN (SELECT ID FROM ProjectPage)
GROUP BY PageComment.ParentID

What I want to get as a result, is one comment per project however, this query gives this error:

"[Microsoft][SQL Native Client][SQL Server] Column PageComment.ID is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

I understand what it is saying, but this query would work in MySQL, how would I achieve this in SQL Server?

Copyright Notice：Content Author:「Thomas R」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/1122436/sql-server-group-by-error

Answers

Adrian Godong 2009-07-13T22:29:59

You have to put all columns you are selecting into the GROUP BY clause.\n\nFor example:\n\nSELECT TOP 5 PageComment.ID\nFROM PageComment\nWHERE PageComment.ParentID IN (SELECT ID FROM ProjectPage)\nGROUP BY PageComment.ID\n",

borjab 2009-07-13T22:32:21

If you know that each parentID has just one ID you can make:\n\nSELECT TOP 5 \n MAX(PageComment.ID )\n from PageComment \n WHERE PageComment.ParentID IN (SELECT ID FROM ProjectPage) \nGROUP BY PageComment.ParentID\n",

Nippysaurus 2009-07-13T22:59:44

Run the following query and view the results ...\n\nSELECT TOP 5 PageComment.ID, PageComment.ParentID\nFROM PageComment\nWHERE PageComment.ParentID IN (SELECT ID FROM ProjectPage)\nGROUP BY PageComment.ParentID\n\n\nThen have a think about how grouping would work on that data. We cannot help you with the grouping because we do not know what you are trying to do.",

Guffa 2009-07-13T22:55:46

Yes, MySQL would let you do that, and it would return what you ask for. The problem with that is that what you ask for is unspecific, so the result is equally unspecific. It will return one of the PageComment.ID values from each group, but it won't care which one.\n\nIn SQL Server you have to be more specific and tell exactly which PageComment.ID value it is that you want from each group. For example the one with the lowest value:\n\nselect top 5 min(c.ID)\nfrom PageComment c\ninner join ProjectPage p on p.ID = c.ParentID\ngroup by c.ParentID\n\n\n(Note that I changed the in (select ...) into an inner join.)\n\nYou might want to add an order by also, to specify which five projects you want data from.",

Remus Rusanu 2009-07-13T22:31:30

What exactly are you GROUPING BY for? You don't have any AGGREGATE function in your query (like SUM, COUNT, MAX, MIN etc) so there is no need for GROUP BY. Do you want to ORDER BY by any chance?",