Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

edit

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, http://meta.stackoverflow.com/a/52063/201017

share|improve this question
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? –  HAL9000 Sep 22 '13 at 15:37
add comment

1 Answer

up vote 1 down vote accepted

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

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: 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
show 2 more comments

Your Answer

 
discard

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.