SQL Server GROUP BY error

Question

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?

Answer 1

You have to put all columns you are selecting into the GROUP BY clause.

For example:

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

Answer 2

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?

Answer 3

If you know that each parentID has just one ID you can make:

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

Answer 4

I don't think that query is on the right track at all. In addition to the values you are selecting which aren't attributes of the groups, are you really trying to get the TOP 1 for each parent and you're using the TOP 5 because there are 5 parents?

I think this is what you're looking for:

WITH FirstComment AS (
    SELECT ParentID, MIN(ID) AS ID
    FROM PageComment
    GROUP BY ParentID
)
SELECT ProjectPage.ID, FirstComment.ID
FROM ProjectPage
LEFT JOIN FirstComment
    ON FirstComment.ParentID = ProjectPage.ID

Answer 5

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.

In 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:

select top 5 min(c.ID)
from PageComment c
inner join ProjectPage p on p.ID = c.ParentID
group by c.ParentID

(Note that I changed the in (select ...) into an inner join.)

You might want to add an order by also, to specify which five projects you want data from.

Answer 6

Run the following query and view the results ...

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

Then 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.