Re: Complicated query

On 12/6/2014 9:16 AM, Shelly wrote:

> On 12/6/2014 8:28 AM, Shelly wrote:

> 
> After some research I found a good solution on the net.  (Note: I
> inadvertently left out that the Answers table has a column "Answer" when
> I posed the original question).
> 
> This is for Oracle.  The solution is:
> 
> with data
>  as
>  (
>   select I.*, A.answer, row_number() over (partition by
> observation_card_id order by answer) rn,
>     count(*) over (partition by A.InstanceId) cnt
>   from Answers A
>   join Instances I on (I.ID = A.InstanceId)
>   order by A.QUESTION_ID
>  )
>  select D.*, ltrim(sys_connect_by_path(D.Answer,','),',') Answers
>   from data D
>   where rn = cnt
>   start with rn = 1
>   connect by prior D.Id = D.Id and prior rn = rn-1
>   order by D.Id
> 
> The answers are all in one field, Answeers, and it is a comma separated
> string.
> 
> Thank you to Joe Stefanelli who answered someone else about this on
> stackoverflow.com.
> 

Shelly,

Unfortunately, this uses recursive SQL, which MySQL doesn't support. However, you should be able to do it with the GROUP_CONCAT function in MySQL - see the manual.

I don't have time right now to work the entire query out (wife is pushing me to go shopping :) ), but I can take a look at it later today when we get back.

BTW - I have never found it faster to run multiple queries from a C program than let MySQL do it itself. This is especially true if you're using a remote server. The fact a complex query takes so long is a sure indication of a design issue.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================