Assigning Teams

[kgreer]

Hey Everyone,

I have a issue I am trying to see if someone can help me out. I have a table with the information below:

Name
Total (A Number or Avg Score)
Class (Classify the user based off his score, example: A,B,C or D)

What I am looking for is to loop through the data set and create teams with a user from every class. These teams need to be a average. I do not want to take the first A person, B person, C person and D person to put on Team 1. That would make them the best team.

Has anyone done anything like this before?

Please help.

Thanks

 

[RyanEK]

Hi,

What's your definition of first ie. how are the records ordered? Assuming its by name, try the following.

select Class,
( select top 1 Name
  from (
   select *, row_number() over (order by Name asc) as rowindex
   from [Table] t2 where t2. Class = t1. Class) as t3 
   where t3.rowindex <> 1
) as Name
from [Table] t1
group by Class

This returns a name from each Class which is not the first record.

Ryan

 

[kgreer]

I am considering first to be the person with the highest score in a class.

Example:
Name Total Class
Joe 98 A
Bob 97 A
Mary 95 A
Ken 90 B
Mat 88 B
Rob 86 B
Jim 83 C
Kim 82 C
Tom 82 C
Tim 80 D
Ethan 78 D
Evan 76 D


So now what I need to do, is to get a person from all the groups to make up a team. I hope this helps out.

Ryank --- I will try your query above.