CODEWITHSUNDEEP
sqloracle-database
Ulrik
Ulrik

Reputation: 556

Group by with CLOB in select-statement

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?

Upvotes: 2

Views: 8424

Answers (3)

li-on
li-on

Reputation: 539

You can use sub-queries. Add unique column ID and then:

SELECT t1.col2,t2.col1
  FROM 
      (SELECT max(ID) as IDM, col2 FROM Table1 GROUP BY col2) t1 
  LEFT JOIN 
      Table1 t2
      ON t1.IDM=t2.ID

Upvotes: 0

user330315
user330315

Reputation:

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;

Upvotes: 8

7alhashmi
7alhashmi

Reputation: 924

Try something like this:

SELECT DISTINCT row1, row2 FROM Table1;

Upvotes: -2

Related Questions