sum only some of values

[Anon]

Hello,

I have an interesting problem and I cannot figure out how to solve it....

I'm using mySQL V 3.23.39 and PHP Version 4.0.3pl1...

I have a table like this:

id pts

---

1 450.001 444.65
1 0.00
1 450.00
1 316.16
1 0.00
1 383.65
1 0.00

I need to be able to dynamically sum only the 4 greatest values in the table. The desired result would be:

id pts

---

1 1728.30

I also have an enum column in the table that could be set to 'Y' for the four greatest values, but I can't event figure out how to do that without resorting to some Perl outside of mySQL.

Any ideas are welcomed...

[brandonschnell]

SELECT sum(pts) AS sum FROM table ORDER BY pts DESC LIMIT 4;

[bkiggins]

Brandon,

That code does not work. It sums all the records in the table. LIMIT only limits the number of records displayed, not the number of records summed. I have no other data to use in the where clause.

So the result was something like this:

total

13307.54

Problem is there are 158 other ids, each with up to 12 records...

There may be no way to do this, without resorting to php or perl. I could probably build an array in php and stuff the values into it with multiple select statements, but I was trying to avoid doing that.

[brandonschnell]

yep, i can't think of another way of doing this in SQL without subselects.