Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Group by error
Message
De
23/06/2003 12:04:58
 
 
À
23/06/2003 11:32:56
Cetin Basoz
Engineerica Inc.
Izmir, Turquie
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Titre:
Divers
Thread ID:
00802887
Message ID:
00802947
Vues:
29
Thnaks Cetin,
that gives me the 9 records I expect to see.


Peter


>It doesn't necessarily mean results are wrong. However for perfomance you might try to divide it in 2 SQLs :
>
>SELECT ;
>	pitype_id,;
>	MAX(expdate);
>	FROM PI ;
>	GROUP BY pitype_id ;
>    INTO CURSOR cc0	nofilter
>
>SELECT ;
>	a.pi_id,;
>	a.pitype_id,;
>	a.expdate ;
>	FROM PI a ;
>	inner join cc0 b on a.pitype_id = b.pitype_id ;
>		and a.expdate = b.expdate ;
>	WHERE eqtype_id=2 AND equip_id=2246 ;
>    INTO CURSOR cc1		
>
Cetin
>
>
>>Thanks Cetin,
>>That is a very interesting construct, but..
>>
>>It takes 640 seconds to return an empty cursor which is not correct.
>>
>>Peter
>>
>>
>>>>I need to retrieve the pi_id for the latest expdate by pitype. in vfp 7 I would do:
>>>>
>>>>
>>>>SET ENGINEBEHAVIOR 70
>>>>SELECT ;
>>>>	pi_id,;
>>>>	pitype_id,;
>>>>	MAX(expdate);
>>>>	FROM PI ;
>>>>	WHERE eqtype_id=2 AND equip_id=2246 ;
>>>>	GROUP BY 2;
>>>>	INTO CURSOR cc1
>>>>
>>>>
>>>>in vfp 8 that gives me a group by error
>>>>
>>>>
>>>>SET ENGINEBEHAVIOR 80
>>>>SELECT ;
>>>>	pi_id,;
>>>>	pitype_id,;
>>>>	MAX(expdate);
>>>>	FROM PI ;
>>>>	WHERE eqtype_id=2 AND equip_id=2246 ;
>>>>	GROUP BY 1,2;
>>>>	INTO CURSOR cc1
>>>>
>>>>
>>>>works but gives me all records because pi_id is the unique primary key (which i AM AFTER)
>>>>
>>>>
>>>>What to do?
>>>>
>>>>Peter
>>>
>>>
>>>
>>>SELECT ;
>>>	pi_id,;
>>>	pitype_id,;
>>>	expdate ;
>>>	FROM PI t1 ;
>>>	WHERE eqtype_id=2 AND equip_id=2246 ;
>>>          and expdate = ;
>>>         (select max(expdate) from pi t2 where t1.pitype_id = t2.pitype_id) ;
>>>	INTO CURSOR cc1
>>>
Cetin
Peter Cortiel
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform