Re: Complicated query

On 06/12/14 15:46, Luuk wrote:

> On 6-12-2014 15:45, The Natural Philosopher wrote:
>> On 06/12/14 14:16, 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.
>>>
>> This is the sort of query where I have found that doing it all in
>> application software using multiple queries is quicker, for sub zillion
>> records, than expecting mysql to do it in hours..
>>
>> For < zillion records, buy oracle and a very powerful computer...
>>
>> Don't get me wrong. I love mysql. But it is not best when optimising and
>> executing complex queries. And its not nearly as good at it as a custom
>> program written in (e.g.) C, is.
>>
>>
>>
>
> The query is not complex.

Any subquery is complex for mysql, or at least dog slow.

> The database is....
>
in my case large, but very simple. One big table of 20 fields

> Can you define 'Instance'?  is that the item that someone starts to
> answer questions?
>
> Can you define 'Answers'?  Are those the possible answers (multiple
> choice) for a question? or the given answers in this instance.
>
> This 'complex' problem has nothing to do with mysql being  'less' able
> to do this kind of questions.

-- 
Everything you read in newspapers is absolutely true, except for the 
rare story of which you happen to have first-hand knowledge. – Erwin Knoll