Aggregate rows based on MIN flag

I’m thinking this is an easy one but the answer is escaping me.

Example: I have a query returning 10 rows, some of which are identical except for the last 2 columns which are a comment and a number(language-flag). I would like to know how to always return the row with the lowest language-flag number for each of these semi identical rows.

I’ve been assuming the idea would be to MIN(language-flag), however the grouping process this requires makes the query still return 2 rows as the comments contents are different.

An example image i created is here:

Any help is appreciated.

Follow up question:

In the confusion I had forgotten why I originally needed to ask this question.

If the comments field happens to be null, when there is the same COL2 with a higher lang-flag, then that should be the selected row, if there is no higher lang-flag row and the comments field is null then just the null comments row should be selected.

I’ve created another example image for this case:

Answer

In Oracle you can also use group by:

select min(comments) keep (dense_rank first order by lang_flag) as comments,
       col1, col2,
       min(lang_flag) as lang_flag
from t
group by col1, col2;

If you have a large amount of data, it might be interesting to see which of the three suggested methods are fastest.

Advertisement

Answer