Column 'Comments.Text' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Question

This question: Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause | Stack Overflow have explained the root of the problem very well and even advised to use max aggregate as a solution. But, lets take the example

group_id    item_id     comment
--------------------------------
1       abc     "blah-blah"
1       def     "tral-la-lah"
1       ghi
2       jkl
2       mno
2       pqr

I understand the solution: I must take an item to represent the group by max(item_id). But how do I take the comment of the selected item? How does it help me to take the corresponding comment?

edit

Everything is like in this question: Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause | Stack Overflow. The only complication that I address here is the extra comment column that I want to select along with the max(item_id) group by group_id.

I am using this beast, Which tools and technologies are used to build Data Explorer? | Meta Stack Exchange

Answer 1

Your question isn't really clear, but I think you want something like this:

select group_id, 
       item_id, 
       comment
from (
   select group_id,
          item_id, 
          comment, 
          row_number() over (partition by group_id order by item_id) as rn
   from the_unknown_table
) t
where rn = 1;

(you didn't state your DBMS, so this is ANSI SQL)

SQL Fiddle demo: http://sqlfiddle.com/#!12/a8471/1