Eliminating the min or max value from a Query result

[wcrimi]

I have query that selects the 3 most recent test scores for each person by date from a table. That's 1 record per date for a total of 3 records per person. That query works fine.

What I want to do is identify the minimum test score for each person, ignore it, and average the other 2 (I have a similar application where I want to eliminate the maximum score).

Is there a standard (and hopefully easy) technique for inputting a query result of 3 or more records like that into another query that will eliminate the max or min, group the other 2, and average them?

Thanks

[orange]

What have you tried? What result?

[wcrimi]

I started off with the 3 records being selected and averaging them. Then I started realizing that I am also going to want to eliminate high and lows at times in this and other applications. I started playing around where I selected the max value and then joined that table to the original query result of 3 records hoping I could use a join to do it, but that doesn't appear to be the correct approach unless I am messing something up. The only other idea I had was to try to use the max value records as a way to delete them from the 3 records, but I didn't want to go that route in case there's a better approach that I just don't know about.

[orange]

Here are some examples using SQL:

To find the average for Jim

(This includes all scores)

SELECT peoplescores.person, Sum(score)/Count(score) AS RequiredAverage
FROM peoplescores
WHERE (((peoplescores.[person])="Jim")
GROUP BY peoplescores.person;

This excludes the Max(score) that Jim had

SELECT peoplescores.person, Sum(score)/Count(score) AS RequiredAverage
FROM peoplescores
WHERE (((peoplescores.[person])="Jim") AND ((peoplescores.[score])<>(select max(score) from peoplescores where person = "JIM")))
GROUP BY peoplescores.person;

This excludes the Min(score) Jim had

SELECT peoplescores.person, Sum(score)/Count(score) AS RequiredAverage
FROM peoplescores
WHERE (((peoplescores.[person])="Jim") AND ((peoplescores.[score])<>(select min(score) from peoplescores where person = "JIM")))
GROUP BY peoplescores.person;

You have to replace the field and tables names with your own.

This was the table I used for test

ID	person	testdate	score
1	jim	22/04/2016	78
2	tom	22/04/2016	78
3	jim	24/04/2016	59
4	jim	28/04/2016	66
5	tom	23/04/2016	98
6	tom	29/04/2016	88

[wcrimi]

Thanks. I'll give that a try tonight. This will work great if I want to get more elaborate and eliminate both max and min too.

[wcrimi]

If I don't want to hard code a name and want it to do the same thing for every person in the table how do I change that? One example should suffice.

[orange]

Try this ignoring the min(score) for the person

select person, sum(score)/count(score)   as RAvg 
FROM Peoplescores
WHERE score <> (select min(x.score) FROM Peoplescores X where x.person =peoplescores.person)
  GROUP BY Peoplescores.person

[wcrimi]

It's not working for me. As soon as I changed the field names I got syntax errors. If I managed to get past that the query started running but seemed to be in a loop.

One of the issues I always run into is my naming conventions. The query that produces the table of 3 records is called Running Styles1.

Those two word names seem to require [Running Styles1].fieldname or [Running Styles1].[fieldname]

Once I start changing your code to cope with that it creates syntax errors.

I seem to go down this path every single time I try to create a query that is not generated by the Wizard. It pretty much never works right. I sometimes do things in multiple steps that could probably easily be done in one step just so I can allow the Wizard to generate the code. It's very frustrating.

[wcrimi]

Update: I got one of them to work: This one had an extra "(" in it next to the WHERE in your code. I'll see if I can make any progress on the others, but the final one is the most important and that one seemed to loop.


SELECT peoplescores.person, Sum(score)/Count(score) AS RequiredAverage
FROM peoplescores
WHERE (((peoplescores.[person])="Jim")
GROUP BY peoplescores.person;

Edit update: I just realized that none of this will work. It's more complicated than my initial presentation because each name can appear more than 3 times. It has to be grouped on other fields also. Each is a group of 3, but the same name can be in more than one group. I got that to work. So let me just continue playing with it and see if I can get the min/max to work and then more importantly the one that goes through the whole table for all names.

[orange]

The sql in post 7 deals with all persons, not hard coded. Use that.

You have seen many posts that advise you to NOT have spaces in field names.

[wcrimi]

The code in #7 was either extremely slow or looping. I'll give it another try tomorrow.

I am learning my lesson about spaces in field names, but sometimes without spaces the names become almost meaningless when a table has 100 or more fields, you have multiple tables, and you are trying to remember what something means months later. Thanks for the help so far. At least I am making progress.

[orange]

A table with 100 fields is probably a good example of a poorly designed table.
Tables are about a single thing and the attributes of that thing along with a few identifiers to related tables. All relational database tables should be normalized.

Good luck with your project.

[wcrimi]

I hear you, but the data comes from an external source and involves a great deal of information that is organized in a fairly logical way already.

[wcrimi]

I was able to get the code in #7 to work correctly for me with a few little tweaks for what I was trying to accomplish. The one small issue I had is that when I ran it against my entire database, it just seemed to hang. When I ran it with a smaller sample it was very quick. My typical use for this query will be a weekly update. So that shouldn't be any problem. To get the historical data for the entire database (2 years of data), I may just have to break it up into smaller pieces by using a date range. As a one time job, that will be fine. Thanks again. This is going to open a whole new area of study for me. Wayne

[orange]

Happy to help.
Good luck with your project.