Group by with CLOB in select-statement

Question

My select-statement looks like:

Select column1, column2
From Table1
Group By column2

column1 is a CLOB and I want to receive one of the values that is part of one group. I know they are all the same so it doesn't matter which one I get. I've tried functions like MIN and MAX but they don't accept CLOB as a type.

To be clear I don't want to aggregate the CLOBs just pick one of them.

This is a simplification of the actual SELECT statement and the GROUP BY clause is necessary.

So with this data:

column1       column2
qwerty          1
qwerty          1
asdfgh          2
asdfgh          2

I want to get:

qwerty          1
asdfgh          2

Any idea how this could be done?

Answer

A CLOB value cannot be used for grouping or inside a distinct clause.

The only chance you have is to convert the CLOB to a varchar but that means you cannot compare the complete contents of the column (note: those are columns , not rows). If you are certain that all your CLOB values are smaller than 8000 bytes, you can use something like this:

select min(dbms_lob.substr(column1)), column2
from foo
group by column2;