Query question... find most occurances of offices

[ehicks727]

I have a situation where I need to identify a 'primary county' based on number of offices. The logic is that if a doctor has a several offices, the county with the most number of offices is the 'primary county' in which he/she is located. If there is a tie, then arbitrarily pick a county (first occurance is fine). Can this be done in a single query? Or is this a multi-step deal?

I guess the only fields that are important in this are the doc's ID and the COUNTY_FIPS field (which is a unique county code), and then a PRI_CNTY field, which will be marked with a "Y" if it is the primary county.

I would appreciate help working through this query/process. I'm not exactly sure where to begin on this one.

Thank you.

 

[hvass]

Does not look like the kind of thing you can do with a single query... why has it got to be a single query? dont be afraid to use temporary tables etc.. they are not necessarily slow or you could use a stored proceedure

 

[ingresman]

You might get away with using group by and having clauses on a select. These allow you to do a kind of order by but in groups of simmilar things (e.g. the doc's ID)
Have a look at an SQL tutorial site for a starter.
hope this helps !