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
tonMachine100Asked:
Who is Participating?
 
sdstuberCommented:
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
0
 
sdstuberCommented:
I see no attachments.  Please don't post screen images though.  Please copy/past text
0
 
slightwv (䄆 Netminder) Commented:
>>please see attached -

Forget to attach?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
tonMachine100Author Commented:
here we go - sorry about that
EE-EXAMPLE-12MAR12.xls
0
 
awking00Commented:
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;
0
 
sdstuberCommented:
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
0
 
awking00Commented:
Gotcha,
I was just looking at the input and desired output and didn't really analyze the question.
0
 
sdstuberCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.