sql strange, VSTUDIO and V4MD 4.3

[Ivan Smahin]

Hello Christian,

Wednesday, June 9, 2010, 2:58:57 PM, you wrote:


> Le 9 juin 2010 à 09:56, Ivan Smahin a écrit :

>> Hello Christian,
>> 
>> Wednesday, June 9, 2010, 2:47:20 AM, you wrote:
>> 
>>> Hello,
>> 
>>> I have this request :
>>> SELECT t1.idScrap, (t1.pertinence + t2.pertinence) as s FROM
>>> kw_in_scrap as t1, kw_in_scrap as t2 WHERE (t1.kw = 'musique' and
>>> t2.kw = 'cuivre' and t1.idScrap = t2.idScrap) ORDER BY s DESC
>> 
>>> but I have wrong values for s in the results and I don't know why.
>> 
>>> Example :
>>> idscrap, kw, pertinence
>>> 1, musique, 1
>>> 1, cuivre, 0.25
>>> 1, son, 0.5
>>> 2, musique, 1
>>> 2, bruit, 0.25
>>> 2, cuivre, 0.3333
>> 
>>> I want to have :
>>> idscrap, s
>>> 1, 1.25
>>> 2, 1.3333
>> 
>>> but I have
>>> 2, 0.6666
>>> 1, 0.5
>> 
>> Seems to be a bug. Please report it to the Mantis.
>> 
>> 
>>> second problem : in Director, order by s is ignored and Valentina make an order by recid in fact...
>> 
>> You   mean   it  is expected sort order with vStudio and no sort order
>> with V4MD?

> yes, but in two cases, "s" is not correct

Agree. It will be fixed ASAP. Please report it to Mantis.

>> If  so  then  you should just update your V4MD -  because it was fixed
>> already.

> in which version?
> have you an url ?

I guess it was in 4.5

BTW the workaround is -

Group all the records you want and sum pertinence.
Something like this:

SELECT
        idScrap, 
        SUM(pertinence) as s 
FROM 
        kw_in_scrap
WHERE 
                kw = 'musique' 
        OR  kw = 'cuivre' 
GROUP BY
        idScrap
ORDER BY 
        s DESC                                                  


-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com