Query has me stumped

[Thrash]

Specifically MySQL. I have the following table:

CREATE TABLE IF NOT EXISTS `Scores` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `datedone` date NOT NULL,
  `score` int(11) NOT NULL,
  `timetaken` int(11) NOT NULL,
  `latitude` float NOT NULL,
  `longitude` float NOT NULL,
  PRIMARY KEY (`id`)
)

This records a score for a user, one score per day. I want to aggregate the score for each user but only show a specific users aggregated score, the 5 users with a next higher score and the five users with the next lower scores, so 11 records in total. In other words it will be a segment of total scores around a specific users score, including the use.

Edit: I'll post my query, which now works but I'm still after a simpler solution:

/*set the userid of the score queried*/
set @userid=10;

/*get that users total score for year*/
set @score=(
	select sum(score)
	from Scores
	where year(datedone)=year(now()) and userid=@userid
);

/*order the record set by the total score*/
select * from(
	/*get upper range*/
	(select userid,total from (
		select userid,sum(score) as total
		from Scores
		where year(datedone)=year(now())
		group by userid) as subq1
	where total>@score
	order by total asc
	limit 0,5)

	union
	/*get lower range*/
	(select userid,total from (
		select userid,sum(score) as total
		from Scores
		where year(datedone)=year(now())
		group by userid) as subq2
	where total<@score
	order by total desc
	limit 0,5)

	union
	/*get users record*/
	(select userid,sum(score) as total
	from Scores
	where year(datedone)=year(now()) and userid=@userid)
) as subq3
order by total desc;

Does anyone have a nice simple query that would drag this information out, without using a page long query that seems to be a sledgehammer to crack a nut? Been looking at this too long now and can't see the wood for the trees.

 

[topdog@OC]

A simpler query requires a more sophisticated set of SQL instructions to be implemented, which don't exist in MySQL (probably never will, as Oracle would rather you buy the 'Oracle' database for that capability than go to the effort of building it into MySQL as well). In terms of solutions whilst the text is verbose so long as it performs adequately, it's probably the best solution you'll have.

Others depend on hacks (updating and referring to the same variables simultaneously in a statement in order to generate a rank, which may create indeterministic result errors), so given that alternative, I'd prefer to use the longer query knowing it can be depended upon.

 

[Thrash]

Thanks for that, confirms what I thought, in MySQL it needs to be done the hard way. I've added more complications to the query as I also now want the result ranked, i.e. shows the ranking in relation to the entire result set. Got it working but will need to see if it performs ok.