sql question

I''m not sure if this is what you mean, but you can select the largest element from a column with the max() function.

well basically ive got a "man" table and a "horse" table.

horse has a foreign key of man_no a primarykey in "man".

That foriegn key is for finding the men who own a horse.

eg
horse_no :: man_id
0 :: 1
1 ::1
2 :: 3

so man 1 owns 2 horses, 0 and 1.


and i find the count that
is 2 horses for man 1
and 1 horse for man 3

Then i need to display the man with the most horses which is man 1

thats what i cant do

Hi, have you tried this?

SELECT count(h.M_id) as Horses_Owned, h.M_id as Man_ID
from HORSE h
group by h.M_id
having count(h.M_id) = max(count(h.M_id))


notice that you don't have to join the two tables if your only looking for a count and an id.



[edited by - hapaboy55 on November 25, 2003 2:21:18 AM]