0

Refer to this question.

I am interested in selecting a DISTINCT NUMERIC from EVENTLOG that has an accompanying CLOB from EVENT_INFO_DETAIL that is not intended to be a DISTINCT selection.

In other words, I am interested in selecting a DISTINCT EVENTID_NBR because of the presence of multiple of the same number and to also see the CLOB associated with that DISTINCT EVENTID_NBR. I am NOT trying to select a DISTINCT CLOB in any way. How might I go about doing this?

For example, a result set could look like the following:

EVENID_NBR    INPUT_ARGS (BYTE SIZE)

143           4096
143           3284
143           2162
143           2222
143           1024

I would want only one EVENTID_NBR because I need a representative sample in my result set but I also need the CLOB selected along with that DISTINCT EVENTID_NBR.

Is there a way to select just the first ROWID of each EVENTID_NBR that also include the associated CLOB?

Should I select a DISTINCT EVENTID_NBR and then try to find the CLOB associated with the EVENTID_NBR I have chosen?

Community
  • 1
  • 1
Mushy
  • 2,535
  • 10
  • 33
  • 54

1 Answers1

0

This is typically done with a window function: 

select evenid_nbr,
       input_args
from (
  select evenid_nbr,
         input_args,
         row_number() over (partition by evenid_nbr order by rowid) as rn
  from event_info_detail
) t
where rn = 1

By changing the order by part you can adjust which evenid_nbr gets returned.