Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

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
0
tonMachine100
Asked:
tonMachine100
1 Solution
 
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
 
tonMachine100Author Commented:
here we go - sorry about that
EE-EXAMPLE-12MAR12.xls
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now