August 2, 2003 at 9:25 am
I consider myself moderately adept at SQL, although I'm wholly self-taught. However, the solution to one common problem has always eluded me. Imagine you have a table:
User Office Count
MAD NYC 50
MAD SFO 200
MAD CHI 75
SAM NYC 16
SAM HOU 19
SAM LON 19
USR SFO 8
Given this data, how do you write a query to give you which office each user has the highest count in? In other words, what I want is:
User Office
MAD SFO
SAM HOU
USR SFO
Clearly I'm throwing a bit of a curveball in here because SAM has 19 docs in both HOU and LON. I've tried every permutation on MAX and TOP I can come up with -- you could, I guess, write a cursor that repeatedly runs SELECT TOP 1 User, Office ORDER BY Count DESC but that seems somehow unpure. Any insights?
August 2, 2003 at 4:37 pm
How about:
SELECT DISTINCT ,
(SELECT TOP 1 [office] FROM UserOfficeCount WHERE = A. ORDER BY [count] DESC) [office]
FROM UserOfficeCount A
Cheers,
- Mark
Cheers,
- Mark
August 3, 2003 at 3:47 am
That did the trick, Mark -- thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply