We help IT Professionals succeed at work.

Repeating Value

please see attached -

fig a current output (select * from o_questions) and fig B the desired output.

I would like the assessments mee_id to repeat for every row of the assessment (ASM).

So the sql needs to check the assessments avd_data value (where the anv_name = 'MEE_ID') and repeat id for every row of the assessment.

It would use partition by but I need some more help on the syntax.

thanks
Comment
Watch Question

SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
I see no attachments.  Please don't post screen images though.  Please copy/past text
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>please see attached -

Forget to attach?

Author

Commented:
here we go - sorry about that
EE-EXAMPLE-12MAR12.xls
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
try this...

S ELECT o.*,
       LAST_VALUE(CASE WHEN anv_name = 'MEE_ID' THEN avd_data END IGNORE NULLS)
           OVER (PARTITION BY asm_id ORDER BY qst_ord)
           meet_id
  FROM o_questions o
awking00Information Technology Specialist
BRONZE EXPERT

Commented:
sdstuber,
Couldn't you have just used -
select o.*,
first_value(avd_data) over (partition by asm_id order by qst_ord) meet_id
from o_questions o;
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
yes, with the data presented in that exact form however the question said to base it on (anv_name = 'MEE_ID')    not simply first row of each partition
awking00Information Technology Specialist
BRONZE EXPERT

Commented:
Gotcha,
I was just looking at the input and desired output and didn't really analyze the question.
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
but... what might be even better than FIRST_VALUE or LAST_VALUE is this...

MAX(CASE WHEN anv_name = 'MEE_ID' THEN avd_data END) OVER (PARTITION BY asm_id)

that way the MEE_ID can appear at any position in the partition

Explore More ContentExplore courses, solutions, and other research materials related to this topic.