CODEWITHSUNDEEP
oracle-databasegroup-byclob
beginner_
beginner_

Reputation: 7622

Oracle: add CLOB to selection when using GROUP BY

I have a query similar to 

SELECT
  s.id,
  max (s.status) AS status,
  s.clob
FROM
  my_table s
INNER JOIN
 my_table_2 s2
ON
  s.id = s2.id  
GROUP BY
  s.id
ORDER BY
  s.status DESC

The real query is a bit more complex but it is not relevant here. The issue I have is that it works fine if I omit s.clob from the selection. When I add it I get an error:

not a GROUP BY expression"

But I can't group on a clob. How can I select this desired data?

EDIT: Note that an issue is that ìdand statusstatus` contains duplicates, eg. both are the same (also true for clob column). I only want to select one of the identical rows but distinct also does not work on clob

Upvotes: 0

Views: 129

Answers (1)

mikron
mikron

Reputation: 683

Try this 

select R.Id, R.Status, R.clob
  from (select row_number() over(partition by s.id order by status desc) Rnum,
                s.id, s.status, s.clob
           from my_table s
          inner join my_table_2 s2 on s.id = s2.id) R
 where R.Rnum = 1

Here you are assigning rownum values for each duplicate row group. And in the resultset choosing only one value per group. Hope this will help you.

Upvotes: 3

Related Questions