How to use GROUP BY on a CLOB column with Oracle?

问题:

I'm trying to combine the GROUP BY function with a MAX in oracle. I read a lot of docs around, try to figure out how to format my request by Oracle always returns:

ORA-00979: "not a group by expression"

Here is my request:

SELECT A.T_ID, B.T, MAX(A.V) 
FROM bdd.LOG A, bdd.T_B B
WHERE B.T_ID = A.T_ID
GROUP BY A.T_ID
HAVING MAX(A.V) < '1.00';

Any tips ?

EDIT It seems to got some tricky part with the datatype of my fields.

• T_ID is VARCHAR2
• A.V is VARCHAR2
• B.T is CLOB

回答1:

I'm very familiar with the phenomenon of writing queries for a table designed by someone else to do something almost completely different from what you want. When I've had this same problem, I've used.

GROUP BY TO_CHAR(theclob)

and then of course you have to TO_CHAR the clob in your outputs too.

Note that there are 2 levels of this problem... the first is that you have a clob column that didn't need to be a clob; it only holds some smallish strings that would fit in a VARCHAR2. My workaround applies to this.

The second level is you actually want to group by a column that contains large strings. In that case the TO_CHAR probably won't help.

回答2:

Try this:

SELECT A.T_ID, B.T, MAX(A.V) 
FROM bdd.LOG A, bdd.T_B B
WHERE B.T_ID = A.T_ID
GROUP BY A.T_ID, B.T
HAVING MAX(A.V) < 1;

回答3:

After some fixes it seems that the major issue was in the group by

YOu have to use the same tables in the SELECT and in the GROUP BY

I also take only a substring of the CLOB to get it works. THe working request is :

    SELECT TABLE_A.ID,
       TABLE_A.VA,
       B.TRACE
FROM
(SELECT A.T_ID ID,
          MAX(A.V) VA
   FROM BDD.LOG A
   GROUP BY A.T_ID HAVING MAX(A.V) <= '1.00') TABLE_A,
                                                                BDD.T B
WHERE TABLE_A.ID = B.T_id;

回答4:

WITH foo as (
  SELECT A.T_ID, B.T, MAX(A.V) maxav
  FROM bdd.LOG A, bdd.T_B B
  WHERE B.T_ID = A.T_ID
  GROUP BY A.T_ID, B.T
)
SELECT * FROM foo WHERE maxav < 1

回答5:

This response is a little late, but for those who need values besides the grouping value and the Maximum criteria column, you can use ROW_NUMBER() over a partition to get what you want:

SELECT T_ID, T, V
FROM 
(
 SELECT A.T_ID, B.T, A.V, ROW_NUMBER() OVER (PARTITION BY A.T_ID ORDER BY to_number(A.V) DESC) rownumber
 FROM bdd.LOG A, bdd.T_B B
 WHERE B.T_ID = A.T_ID
)
WHERE rownumber = 1

Don't forget the DESC modifier on the ORDER BY to get the Maximum value; without it you get the Minimum value. If A.V is nullable you will also need to wrap it in NVL() or you'll just get NULLs; NULL values always sort first (at least in Oracle SQL) regardless of if you choose ascending or descending order.

回答6:

you can if possible transform the clob into the PK if possible and than to an select on the PK. This is even faster according to execution plan then rowid. I this case i need the first not empty clob. So i say if clob is not empty use the pk else null. The result is not an clob and i can fetch the clob in an outer query.

select a.* ,r.DESCRIPTION 
from (
select distinct
FIRST_VALUE(
  case 
     when a.DESCRIPTION is null then null 
     else PK_COL 
  end 
  IGNORE NULLS) 
  OVER (ORDER BY a.sort_col desc ROWS between UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) DESCRIPTION_PK, 
group_column
from reporting a where group_column='xyz) a
join reporting r on (r.PK_COL=a.DESCRIPTION_PK);