Group by with CLOB in select-statement

by

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;

More Related Contents:

  1. Receive an unexpected symbol error in my PLSQL query [closed]
  2. Select First Row of Every Group in sql [duplicate]
  3. Oracle: how to UPSERT (update or insert into a table?)
  4. Get top results for each group (in Oracle)
  5. Comparing Dates in Oracle SQL
  6. Oracle SQL query: Retrieve latest values per group based on time [duplicate]
  7. SQL – How to store and navigate hierarchies?
  8. GROUP BY / aggregate function confusion in SQL
  9. Oracle “(+)” Operator
  10. How do you create a temporary table in an Oracle database?
  11. What exactly do quotation marks around the table name do?
  12. How can I get column names from a table in Oracle?
  13. How to store only time; not date and time?
  14. What’s the difference between RANK() and DENSE_RANK() functions in oracle?
  15. How to check the maximum number of allowed connections to an Oracle database?
  16. Select without a FROM clause in Oracle
  17. Is there any way to flush output from PL/SQL in Oracle?
  18. PLSQL Insert into with subquery and returning clause
  19. SELECT from table with Varying IN list in WHERE clause
  20. SELECT INTO using Oracle
  21. Display names of all constraints for a table in Oracle SQL
  22. `show create table` equivalent in oracle sql
  23. Does ‘Select’ always order by primary key?
  24. Query json dictionary data in SQL
  25. ORA-00972 identifier is too long alias column name
  26. Search an Oracle database for tables with specific column names?
  27. ORA-01861: literal does not match format string
  28. Query a Table’s Foreign Key relationships
  29. Backup SQL Schema Only?
  30. IN vs OR of Oracle, which faster?

Leave a Comment