SEO

I have a database with a table called: RESULTS that has the following
fields: DIVISION, NAME, SCORE. I want to SUM up the top 5 scores for
each DIVISION/NAME. I cant seem to get anything to work in MYSQL. I
have seen a rank function used Oracle and stuff along that line, but
not available on MYSQL. Here is what I tried without any luck:

SELECT r1.division, r1.name,
(SELECT SUM(r2.score) FROM results r2 WHERE
r1.division=r2.division AND r1.name=r2.name
GROUP BY r2.division, r2.name
ORDER BY r2.division, r2.name, r2.score DESC LIMIT 5) AS total
FROM results r1
ORDER BY r1.division, r1.name, total DESC;


Can anyone get something like this working on MYSQL v5.0? Help.

On 18 Jun, 10:06, lefeb...@iwavesolutions.com wrote:
> I have a database with a table called: RESULTS that has the following
> fields: DIVISION, NAME, SCORE. I want to SUM up the top 5 scores for
> each DIVISION/NAME. I cant seem to get anything to work in MYSQL. I
> have seen a rank function used Oracle and stuff along that line, but
> not available on MYSQL. Here is what I tried without any luck:
>
> SELECT r1.division, r1.name,
> (SELECT SUM(r2.score) FROM results r2 WHERE
> r1.division=r2.division AND r1.name=r2.name
> GROUP BY r2.division, r2.name
> ORDER BY r2.division, r2.name, r2.score DESC LIMIT 5) AS total
> FROM results r1
> ORDER BY r1.division, r1.name, total DESC;
>
> Can anyone get something like this working on MYSQL v5.0? Help.

Always a good idea to search the group first:

http://groups.google.co.uk/group/com...51f4fe4 e03d0

On Mon, 18 Jun 2007 02:06:50 -0700, lefebvre@iwavesolutions.com wrote:

>I have a database with a table called: RESULTS that has the following
>fields: DIVISION, NAME, SCORE. I want to SUM up the top 5 scores for
>each DIVISION/NAME. I cant seem to get anything to work in MYSQL. I
>have seen a rank function used Oracle and stuff along that line, but
>not available on MYSQL. Here is what I tried without any luck:
>
>SELECT r1.division, r1.name,
> (SELECT SUM(r2.score) FROM results r2 WHERE
>r1.division=r2.division AND r1.name=r2.name
> GROUP BY r2.division, r2.name
> ORDER BY r2.division, r2.name, r2.score DESC LIMIT 5) AS total
>FROM results r1
>ORDER BY r1.division, r1.name, total DESC;
>
>
>Can anyone get something like this working on MYSQL v5.0? Help.

Your LIMIT may not limit the right thing : it will limit the number of
lines you will get in your inner SELECT, but you will anyways get only
one because of your two clauses in the WHERE.

I think it would be more tidy to do it with a FUNCTION.

CREATE FUNCTION `sum_of_five`(_division INT,
_name VARCHAR(100)) RETURNS int(11)
BEGIN
DECLARE _res INT;

SELECT SUM(score) INTO _res
FROM (
SELECT r2.score AS FROM results r2
WHERE r1.division=r2.division AND r1.name=r2.name
ORDER BY r2.score DESC LIMIT 5
) AS tmptable;

RETURN _res;

END$$

And then :

SELECT r1.division, r1.name, sum_of_five(division,name)
FROM results r1
ORDER BY r1.division, r1.name, total DESC;

(Not tested, maybe some typos, but it's the idea.)

On Mon, 18 Jun 2007 17:39:28 +0800, subtenante
<zzsubtenantezz@gmail.com> wrote:


>SELECT SUM(score) INTO _res
>FROM (
> SELECT r2.score AS FROM results r2
> WHERE r2.division=_division AND r2.name=_name
> ORDER BY r2.score DESC LIMIT 5
>) AS tmptable;

I was right i made some mistakes ! Corrected.

On Jun 17, 11:45 pm, subtenante <zzsubtenant...@gmail.com> wrote:
> On Mon, 18 Jun 2007 17:39:28 +0800, subtenante
>
> <zzsubtenant...@gmail.com> wrote:
> >SELECT SUM(score) INTO _res
> >FROM (
> > SELECT r2.score AS FROM results r2
> > WHERE r2.division=_division AND r2.name=_name
> > ORDER BY r2.score DESC LIMIT 5
> >) AS tmptable;
>
> I was right i made some mistakes ! Corrected.


Thank you for ALL your help. How can this be accomplished NOT using a
FUNCTION? My ISP does not allow FUNCTIONS on MySQL for security
reasons (not sure why thats an issue) but none the less, I appreciate
any insite to your solution without functions.

Here is a test environment setup. See if this can be done with TOP 3
just for brevity sake:

DROP TABLE games;
CREATE TABLE games (
pkey int(11) NOT NULL auto_increment,
division varchar(15),
user varchar(15),
payout int,
PRIMARY KEY (pkey)
);

INSERT INTO games (division, user, payout) VALUES ('A','fred',11);
INSERT INTO games (division, user, payout) VALUES ('A','fred',2);
INSERT INTO games (division, user, payout) VALUES ('A','fred',13);
INSERT INTO games (division, user, payout) VALUES ('A','fred',4);
INSERT INTO games (division, user, payout) VALUES ('A','fred',5);
INSERT INTO games (division, user, payout) VALUES ('A','matt',1);
INSERT INTO games (division, user, payout) VALUES ('A','matt',21);
INSERT INTO games (division, user, payout) VALUES ('A','matt',31);
INSERT INTO games (division, user, payout) VALUES ('A','matt',14);
INSERT INTO games (division, user, payout) VALUES ('A','matt',5);
INSERT INTO games (division, user, payout) VALUES ('B','tom',13);
INSERT INTO games (division, user, payout) VALUES ('B','tom',20);
INSERT INTO games (division, user, payout) VALUES ('B','tom',23);
INSERT INTO games (division, user, payout) VALUES ('B','tom',9);
INSERT INTO games (division, user, payout) VALUES ('A','tom',15);
INSERT INTO games (division, user, payout) VALUES ('B','rick',18);
INSERT INTO games (division, user, payout) VALUES ('B','rick',29);
INSERT INTO games (division, user, payout) VALUES ('B','rick',21);
INSERT INTO games (division, user, payout) VALUES ('B','rick',8);
INSERT INTO games (division, user, payout) VALUES ('B','rick',25);

Thanks again!!

On 19 Jun, 01:55, lefeb...@iwavesolutions.com wrote:
> On Jun 17, 11:45 pm, subtenante <zzsubtenant...@gmail.com> wrote:
>
> > On Mon, 18 Jun 2007 17:39:28 +0800, subtenante
>
> > <zzsubtenant...@gmail.com> wrote:
> > >SELECT SUM(score) INTO _res
> > >FROM (
> > > SELECT r2.score AS FROM results r2
> > > WHERE r2.division=_division AND r2.name=_name
> > > ORDER BY r2.score DESC LIMIT 5
> > >) AS tmptable;
>
> > I was right i made some mistakes ! Corrected.
>
> Thank you for ALL your help. How can this be accomplished NOT using a
> FUNCTION? My ISP does not allow FUNCTIONS on MySQL for security
> reasons (not sure why thats an issue) but none the less, I appreciate
> any insite to your solution without functions.
>
> Here is a test environment setup. See if this can be done with TOP 3
> just for brevity sake:
>
> DROP TABLE games;
> CREATE TABLE games (
> pkey int(11) NOT NULL auto_increment,
> division varchar(15),
> user varchar(15),
> payout int,
> PRIMARY KEY (pkey)
> );
>
> INSERT INTO games (division, user, payout) VALUES ('A','fred',11);
> INSERT INTO games (division, user, payout) VALUES ('A','fred',2);
> INSERT INTO games (division, user, payout) VALUES ('A','fred',13);
> INSERT INTO games (division, user, payout) VALUES ('A','fred',4);
> INSERT INTO games (division, user, payout) VALUES ('A','fred',5);
> INSERT INTO games (division, user, payout) VALUES ('A','matt',1);
> INSERT INTO games (division, user, payout) VALUES ('A','matt',21);
> INSERT INTO games (division, user, payout) VALUES ('A','matt',31);
> INSERT INTO games (division, user, payout) VALUES ('A','matt',14);
> INSERT INTO games (division, user, payout) VALUES ('A','matt',5);
> INSERT INTO games (division, user, payout) VALUES ('B','tom',13);
> INSERT INTO games (division, user, payout) VALUES ('B','tom',20);
> INSERT INTO games (division, user, payout) VALUES ('B','tom',23);
> INSERT INTO games (division, user, payout) VALUES ('B','tom',9);
> INSERT INTO games (division, user, payout) VALUES ('A','tom',15);
> INSERT INTO games (division, user, payout) VALUES ('B','rick',18);
> INSERT INTO games (division, user, payout) VALUES ('B','rick',29);
> INSERT INTO games (division, user, payout) VALUES ('B','rick',21);
> INSERT INTO games (division, user, payout) VALUES ('B','rick',8);
> INSERT INTO games (division, user, payout) VALUES ('B','rick',25);
>
> Thanks again!!

Errm, I already gave you a link to the article that explains it. Why
are you asking again?