Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dubem1
Starting Member
4 Posts |
 Posted - 2005-12-18 : 23:45:16
|
| Hello Imagine a athlete judged by 10 judges. This athlete will get 10 notes for his performance. Each note will be store in a table. (AthleteNote) I also have the athlete table. The particularity is that the final note of the athlete is the sum of is 8 best notes.What I try to acheieve it's a query that is returning the list of athlete with is fnal note.I can't do a "Sum" on the note column because it will add all of the 10 notes.So how can I just calculate de 8 best notes within a query?Thank youMartin |
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2005-12-18 : 23:49:19
|
| hi,could you please post the table structure alongwith the sample data and the output you are looking for?? |
 |
|
|
dubem1
Starting Member
4 Posts |
Posted - 2005-12-18 : 23:55:42
|
| Table Athlete-------------1 Mark Smith2 Jason SpencerTable AthleteNote-----------------1 91 81 9.51 71 81 41 91 7.51 6.51 52 92 92 6.52 62 82 82 92 102 4.52 7Output------Mark Smith 64.5Jason Spencer 66.5 |
|
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2005-12-19 : 00:25:24
|
| i guess this is what u r looking forselect t.id, sum(t.note)from atheletenote t where t.note in(select top 8 note from atheletenote where id=t.id order by note desc)group by t.id |
|
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2005-12-19 : 00:30:34
|
| u can have an inner join with athlete table as well to get the namesselect a.name, sum(t.note)from athletenote t inner join athlete aon a.id = t.idwhere t.note in(select top 8 note from athletenote where id=t.id order by note desc)group by a.name |
|
|
|
|
|
|
Only calculate some child records