Filtering multiple occurences of 1st, 2nd and 3rd

J

Jody

Hi,

My recordset has the following fields:

ClassID
SessionID
EnrDate
Mark
EntrantID

Unique records are determined by a combination of ClassID SessionID and
EnrDate - which collectively represent a music competition. For each
combination of these three fields, I have to filter out the highest 3 marks.

Unfortunately, the 'Top 3' SQL clause doesn't suffice in this case since the
business rule states that all occurrences of highest, second highest and
third highest have to be reported. 'Top 3' would only report redundancies
for third place. In other words, if the recordset looked like the following:

ClassID SessionID EnrDate Mark EntrantID
1 2 01/01/2005 98 1
1 2 01/01/2005 98 2
1 2 01/01/2005 77 3
1 2 01/01/2005 77 4
1 2 01/01/2005 77 5
1 2 01/01/2005 71 6
1 2 01/01/2005 71 7
1 2 01/01/2005 70 8
2 2 01/01/2005 100 8
2 2 01/01/2005 68 9
2 2 01/01/2005 68 10
2 2 01/01/2005 67 11
2 2 01/01/2005 55 12

I would like to have the following returned:

ClassID SessionID EnrDate Mark EntrantID
1 2 01/01/2005 98 1
1 2 01/01/2005 98 2
1 2 01/01/2005 77 3
1 2 01/01/2005 77 4
1 2 01/01/2005 77 5
1 2 01/01/2005 71 6
1 2 01/01/2005 71 7
2 2 01/01/2005 100 8
2 2 01/01/2005 68 9
2 2 01/01/2005 68 10
2 2 01/01/2005 67 11

notice that the 8th record and the 13th record are filtered out since they
don't qualify.

Any ideas as to what SQL logic, expressions, etc. would produce this result?

Thanks,
Jody
 
Top