0

I would like a query that selects the total calories and Member ID from my table and order it by the total calories in descending order. I require the first 8 values to be printed out as ranking list, however the way I have designed my program is that every time a new record is added it calls the highest total calories value and then adds the new calculated value to it and stores this as the new total calories value.

This system creates a problem though as when I use this query:

SELECT TotalCalories, MemberID
FROM TrainingInfo
ORDER by TotalCalories desc
LIMIT 0,7

It wont fetch the correct amount of values in the case that the member may have two records which are both on the ranking list which I don't want, as I am using the SQL in python for my coursework, I can get rid of duplicate values but it wont give me 8 ranks. I have tried to using DISTINCT however I can't seem to get it to work, the MemberID will be the DISTINCT Value.

If someone could help me with the query that would be great.

Andrey Korneyev
  • 26,353
  • 15
  • 70
  • 71
Bazz
  • 1
  • If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. – Strawberry Apr 28 '15 at 09:19
  • http://stackoverflow.com/questions/5967130/mysql-select-one-column-distinct-with-corresponding-other-columns – mohan111 Apr 28 '15 at 09:20

1 Answers1

1

Are you saying that there could be multiple records for the same memberid? If so, then you would need to sum the totalcalories, and group by memberid:

select Memberid, sum(TotalCalories)
from TrainingInfo
group by MemberID
order by sum(TotalCalories) desc
limit 0,7
mti2935
  • 11,465
  • 3
  • 29
  • 33
  • There are multiple records for the same MemberID as it is used as a foreign key to link to my ContactInfo table and I don't want to sum the total calories field as they value is already summed. – Bazz Apr 28 '15 at 13:38
  • Actually I have used this, instead of summing the TotalCalories field I summed my CaloriesBurnt field. Thanks a lot. – Bazz Apr 28 '15 at 13:52