Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
GROUP BY clause difference between VFP & MSSQL
Message
 
 
À
14/09/2000 19:29:26
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:
00416701
Vues:
23
Imagine situation we want to find MIN value. But we can do nothing just with value, we need ID of record that contains MIN value. In VFP I can write
SELECT MIN(MyNumber), ID_FIELD, category from Mytable group by category.

I don't know how to get ID_FIELD of valid records for SQL Server. Do you know solution?

>>This should be easy for seasoned c/s pros but I have problem getting around it. In MSSQL, columns that appear in the select list "must be" used as arguments of an aggregate function in the GROUP BY clause. For example, in VFP you could do this:
>>
>>SELECT table1.*, table2.field1, table2.field2, table2.field3
>> FROM table1 JOIN table2 ON table1.fieldx=table2.fieldx
>> GROUP BY table2.field2
>>
>>But you can't do this with MSSQL. How do I get around this "feature"?
>
>You do not.
>
>Why use a GROUP BY in the example you gave? There are no aggregated fields, so there's no reason to group by anything. If it's just to get a count of the distinct items in Field2, then the other fields are really extraneous, since the data will be meaningless anyway.
>
>For aggregated fields, it really makes sense to enforce this, if you think about it.
>
>e.g. if you have the following data
>
>Field1   Field2   Field3   Field4
>---------------------------------
>Hello    World    Bob      54
>Hello    World    Fred     89
>Howdy    Pard     John     47
>
>** VFP allowed
>SELECT field1, field2, field3, SUM(field4) AS field4 ;
>  FROM table ;
>  GROUP BY field1, field2 ;
>  INTO CURSOR cuSum
>
>** result set
>Field1   Field2   Field3   Field4
>---------------------------------
>Hello    World    ID1      143
>Bert     Ernie    ID3      47
>Since you wouldn't know that Field3 really had a different value for one of the records involved, Field3 contains meaningless information. If Field3 really is meaningless information, it should not be included in the field list; otherwise, it should be included in the GROUP BY as well.
Vlad Grynchyshyn, Project Manager, MCP
vgryn@yahoo.com
ICQ #10709245
The professional level of programmer could be determined by level of stupidity of his/her bugs

It is not appropriate to say that question is "foolish". There could be only foolish answers. Everybody passed period of time when knows nothing about something.
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform