0

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

2
  • 1
    Please show us your SQL query and the definition of the involved table(s).
    – user1822
    Sep 22, 2013 at 15:12
  • Which dbms are you using?
    – BlitZ
    Sep 22, 2013 at 15:37

1 Answer 1

2

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

7
  • Sorry, I wanted a select grouped by group_id.
    – Val
    Sep 22, 2013 at 15:21
  • @Val: that's what my query does (only a minor typo: goup instead of group_id)
    – user1822
    Sep 22, 2013 at 15:22
  • Amazing. But what is the rn = 1?
    – Val
    Sep 22, 2013 at 15:28
  • @Val: it's a window function that essentially does a group by "on-the-fly". See e.g. here: postgresql.org/docs/current/static/tutorial-window.html
    – user1822
    Sep 22, 2013 at 15:31
  • Does order by item_id change anything if I replace it with order by group_id?
    – Val
    Sep 22, 2013 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.