Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

They 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?


Everything is like in that question. 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,

share|improve this question
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? – Num6 Sep 22 '13 at 15:37
up vote 1 down vote accepted

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

select group_id, 
from (
   select group_id,
          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:!12/a8471/1

share|improve this answer
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: – 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 posting your answer, you agree to the privacy policy and terms of service.

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