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

  • 1
    Please show us your SQL query and the definition of the involved table(s). – a_horse_with_no_name Sep 22 '13 at 15:12
  • Which dbms are you using? – BlitZ Sep 22 '13 at 15:37
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

  • Sorry, I wanted a select grouped by group_id. – Val Sep 22 '13 at 15:21
  • @Val: that's what my query does (only a minor typo: goup instead of group_id) – a_horse_with_no_name Sep 22 '13 at 15:22
  • Amazing. But what is the rn = 1? – Val Sep 22 '13 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 – a_horse_with_no_name Sep 22 '13 at 15:31
  • Does order by item_id change anything if I replace it with order by group_id? – Val Sep 22 '13 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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