Hi,
I think the problem stems from the structure of your data. A normalised way of storing it might be in a table such as this:
CREATE TABLE #Efforts
(
Participant nvarchar(15),
Rank int,
Effort int
)
The data you present would look like this:
Participant Rank Effort
A 1 2
A 2 3
A 3 7
B 1 8
B 2 7
B 3 3
C 1 5
C 2 2
C 3 8
D 1 1
D 2 3
D 3 7
And then your query becomes trivial:
SELECT
*
FROM
#Efforts
WHERE
Effort = 2
which gives
Participant Rank Effort
A 1 2
C 2 2
Hope I've understood your problem and that this solution helps.
Jon