I have a results table

EventID, Catergory, Placed, TeamName, EventYear, points

('1200','Event1',1,'team1','2009', 250),
('629','Event2',25,'team23,'2010', 125),
('145','Event4',33,'team33,'2007', 75),
('1564','Event5',4,'team38,'2002', 200),
('800','Event8',51,'team13,'2001', 25),
('568','Event1',16,'team63,'2006', 80),
('895','Event3',27,'team123,'2003', 120),
('568','Event7',8,'team153,'2002', 175),
('532','Event4',19,'team3,'2001', 90),
('123','Event11',10,'team83,'2000', 140),

The table has a couple hundred thousand records.
I'm trying to get the the top 10 Placed per team based on an event type and year and sum up the points.

So far I have

Select b.totalpoints, Catergory, b.Teamname
From (Select Placed, Sum(Points) as totalpoints, Catergory, Teamname From Event_Results as a
Where EventYear = '2009'
And Catergory = 'Event1'
Group By Teamname
) as b
ORDER BY totalpoints Desc;

Which works fine but it returns the sum of all points for teams. I can't find a place for Limit 10 to work.

Any thoughts or better solutions greatly appreciated.

please run this query, and confirm that it is working correctly to give the top 10 points per team...once you've convinced yourself it's working, replace the ORDER BY clause with and then remove t.Placed from the SELECT clause and replace t.Points with SUM(t.Points)

vwalah

Thanks,
I'll try it.

Ok I tried and and I'm getting server shutdown in progress error 1053. Other queries work.

would you please do a SHOW CREATE TABLE so i can see your indexes

Is this what your looking for?

'Event_Results', 0, 'PRIMARY', 1, 'ID', 'A', 191725, , '', '', 'BTREE', ''
'Event_Results', 1, 'Index_1', 1, 'EventID', 'A', 1101, , '', 'YES', 'BTREE', ''
'Event_Results', 1, 'Index_1', 2, 'TeamName', 'A', 38345, , '', 'YES', 'BTREE', ''
'Event_Results', 1, 'NumberTeams', 1, 'NumberOfTeams', 'A', 132, , '', 'YES', 'BTREE', ''

Thanks for your help.

Or this

'Event_Results', 'CREATE TABLE `Event_Results` (
`EventID` varchar(15) default NULL,
`Catergory` varchar(25) default NULL,
`Placed` int(11) default NULL,
`TeamName` varchar(150) default NULL,
`DisplayOrder` int(11) default NULL,
`Points` int(11) default NULL,
`ID` int(11) NOT NULL auto_increment,
`NumberOfTeams` int(11) default NULL,
`EventYear` varchar(4) default NULL,
PRIMARY KEY (`ID`),
KEY `Index_1` USING BTREE (`EventID`,`TeamName`),
KEY `NumberTeams` USING BTREE (`NumberOfTeams`)
) ENGINE=MyISAM AUTO_INCREMENT=195320 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC'

yeah, that

try this --

That made a difference.
I appreciate your help.


As I don't really under stand your solution. I just want to make sure my wants are clear.

I need the top 10 placed results returned per team. So it can be any placement just the best 10 out of all their results. Then sum up the points for those places.

This seems to be only returning if they placed higher than 10th place.

Thanks again.

are you sure? or did you just look at the number 10 in my solution and make an assumption?

please give me some real test data to work with -- you needn't provide more than 3 or 4 places per team, we don't have to test with the top 10

however, the stuff you posted earlier is not enough to run a meaningful test

I just sent you an email via your website. thanks

i installed andloaded your table, and tested my query

it ran in 1.3 seconds (mysql 4.1 on a 5-yr-old windows desktop)

i spotchecked the data and it's working correctly

maybe we should revisit what your original question was?

in other words, maybe i misunderstood "top 10 placed results"

i was doing it based on the highest 10 values in the Points colulmn

r937,

Thanks I just emailed some screen shots and a better explination. Thanks for your help.