I have 3 fields in myTable like the following.

Code:
 data in myTable

(ID) (name_class_blah_blah) (score)
(1)  (Jane C3 ogdsbc)        (75) 
(2)  (kate C2 ihc)           (65) 
(3)  (Tom C1 kogv)           (85) 
(4)  (Robert C3 kbgefxcg)    (90) 
(5)  (Mary C1 bgfds)         (60)
I like to produce my target output like the following.

Code:
target result

(2) (kate C2 ihc)        (65)
(3) (Tom C1 kogv)        (85)
(4) (Robert C3 jbgefxcg) (90)

There are 2 persons in C1. they are Tom and Mary. Tom gets higher score than Mary. So Tom should be produced.

There are 1 person in C2. She is Kate. No one can get higher score than Kate.
So Kate should be produced.

There are 2 persons in C3. they are Jane and Robert.
Since Robert gets higher score than Jane, Robert should be produced.

The problem here is that the group key word comes in the middle of the values in "name_class_blah_blah" field.

Can I produce my target result with your help?


Thanks in Advance