Eliminating the min or max value from a Query result

[wcrimi]

I have a quick question on this.



Let's say the typical case is that each person has 3 races and I code to eliminate the min value, I assume that if a specific person only has 2 races, it will eliminate the lower of those 2.

What happens if a person only has 1 race? Does it eliminate that one and return nothing?

Is there any way to control it where I say if there are 3, provide the best 2, if there are 2 provide both of those, if there is 1, give me that 1?

[wcrimi]

Here is the SQL from my current Query:

SELECT RS1.RCTrack, RS1.RCDate, RS1.RCRace, RS1.horse,
Sum([2Fsprint4Froute])/Count([2Fsprint4Froute]) AS [AVG],
Sum([4Fsprint6Froute])/Count([4Fsprint6Froute]) AS AVG2
FROM RS1
WHERE (((RS1.[2Fsprint4Froute])<>(select min([x].[2Fsprint4Froute]) FROM [RS1] X where [x].[horse] = [RS1].[horse])))
GROUP BY RS1.RCTrack, RS1.RCDate, RS1.RCRace, RS1.horse;

It takes the group, eliminates the minimum record, and averages a couple of fields of the remaining records. Here's the problem. If the minimum value occurs more than one time, it eliminates all of them. So say I have 4 values that are 11, 10, 9, 8 and I want to eliminate the minimum, that should eliminate the 8, leave me the average of 11, 10 and 9. Perfect. If however, the 4 values are 11, 10, 9, 9 it eliminates both 9s and averages the 11 and 10 when I really want it to average 11, 10 ,and 9. How do I get it to only eliminate a single occurrence?

Thanks

[wcrimi]

Does anyone see the syntax error here?

I have a table called RaceFlowC2a with group of records. All I want to do is eliminate the record from each group with the maximum value (only 1 occurrence if there is ore than one).

The Table will be grouped on Track, [Date Race], and [Race Num]

The field I am interested in is MinOfFRRating. From the group I want to eliminate the record with the maximum value in MinOfFRRating. If there is a better/easier way I am all ears. I've been able to create queries similar to this in the past, but something is miss here.



SELECT RaceFlowC2a.Track, RaceFlowC2a.[Date Race], RaceFlowC2a.[Race Num], RaceFlowC2a.MinOfFRRating
FROM RaceFlowC2a
WHERE ((([RaceFlowC2a].[MinOfFRRating] <>(select max([X].[RaceFlowC2a].[MinOfFRRating])
FROM [RaceFlowC2a] X
where
[x].[RaceFlowC2a].[Track] = [RaceFlowC2a].[Track] and [x].[RaceFlowC2a].[Date Race] = [RaceFlowC2a].[Date Race] and [x].[RaceFlowC2a].[Race Num] = [RaceFlowC2a].[Race Num] and [x].[RaceFlowC2a].[MinOfFRRating] = [RaceFlowC2a].[MinOfFRRating])))
GROUP BY [RaceFlowC2a].[Track], [RaceFlowC2a].[Date Race], [RaceFlowC2a].[Race Num];

[wcrimi]

I'm still struggling with this trying various approaches.

This is the latest.

I have a table with 4 fields

[Track]
[Date Race]
[Race Num]
[MinOfFRRating]

I want to group on [Track], [Date Race] and exclude the max value MinOfFRRating from the output (preferably only a single occurrence if there is more than 1 with the same max value)

The following code is not excluding max value of each group. I believe it's excluding the max value of the entire table.

SELECT Track, [Date Race], Avg(MinOfFRRating) AS AvgOfMinOfFRRating, Count(MinOfFRRating) AS CountOfMinOfFRRating
FROM RaceFlowC2a
WHERE (((MinOfFRRating)<>(select max([X].[MinOfFRRating])
FROM [RaceFlowC2a] X
where
[x].[Track] = [Track] and [x].[Date Race] = [Date Race])))
GROUP BY Track, [Date Race];