>>Mike - I understand this and agree - however, what if country ("USA") were in there too as a joined in table - why should I have to group on country when they are all the same?
>
>How does SQL Server know that all the values are the same?
when there is a join on a unique key -
only the key should have to be in the group - not all the dependant fields that go with the key .... say I've got a city table and a state table joined on an integer key. Suppose I want a count of how many cities I have in each state - I do
select st.name, count(*) from city ct join state st on ct.fkey = st.pkey group by st.name
no problem - but now suppose I have a dozen other fields in the state table - capital city, state flower, population, area, avg temparature, etc. that I also want returned - In SQL Server - I have to include every one of those in the 'group by' where all that is needed for a GUARANTEED UNIQUE set of data per group is the key (or unique field st.name in this case).
>The sorting is done after the Grouping has been calculated. Do you really want SQL Server to sort the data twice to solve you query?
all I can say is that VFP does it and doesn't seem to have any trouble with it - this is one (maybe the only one) aspect of VFP's implementation of SQL that I like better than SQL Server ...
Thanks!
Ken B. Matson
GCom2 Solutions