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.comICQ #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.