Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
GROUP BY clause difference between VFP & MSSQL
Message
De
15/09/2000 09:51:48
 
 
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Divers
Thread ID:
00416688
Message ID:
00416835
Vues:
24
>No. In VFP ID will be appropriate to the record where MIN value found. Its not an omission in SQL for VFP.

Did you use the data from the example I gave you before?
try this:
CREATE CURSOR cutestme ( ;
  iid i, ;
  ccategory c(10), ;
  nvalue n(5,2))

FOR ji = 1 TO 5
  INSERT INTO cutestme ;
    VALUES (ji, "Category", MOD(ji,2))
ENDFOR
LOCATE
BROWSE LAST NOWAIT

SELECT ccategory, iid, MIN(nvalue) AS min_value ;
  FROM cutestme ;
  GROUP BY ccategory ;
  INTO CURSOR cugroup
BROWSE LAST NOWAIT
The result set is
IID = 5
CCATEGORY = "Category"
MIN_VALUE = 0


The record with the IID = 5 does not have NVALUE=0 - so this is not appropriate to the record where it was found.

Grouping by CCATEGORY only in this case will work if:
1) CCATEGORY is unique - but in that case, there's no need to group by it, since you'll get all records back anyway.

2) CCATEGORY, IID combination is always unique - but, then what's the problem with including IID in the GROUP BY list?

3) NVALUE will always have only one record with the minimum value by CCATEGORY, but the chances of this happening as a rule is slim.
Insanity: Doing the same thing over and over and expecting different results.
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform