Get rid of maximun and minimun values among several fields

[marovios]

This is it, say i have six numeric fields to fill in.
What i need is to get rid of the biggest and smallest numbers among these six fields, so i can take into account just the middle four left fields. It's a system for ranking persons performances.

For example:
field#1= 6.3
field#2= 2.3
field#3= 7.5
field#4= 4.9
field#5= 8.3
field#6= 4.5

and i need to get rid of the biggest and lowest entered values, in this case, i should get rid of fields #2 and #5

How can i do this?. any idea?
Thanks

[mitchind]

You're making it hard on yourself with your database design.

ideally you should have only one field for score - you can add others to identify which person is associated with score - and if you need to track who assigned the score, you can add that as an extra field too.

Something like..
userid, scorer, score, score_date

You can probably do it the way you have it but will likely need to build a big union query renaming the field# fields to a common name "score" in the queries. Then you might be able to do a ORDER BY and use the LIMIT clause....

[marovios]

Hi,
Thanks for answering.
The thing is that i need different fields to fill in as it's a ranking system and each field takes a different skill to qualify. One of them qualify for "kindness", other for "beauty", and so on. I think i need several fields.

[cc1030]

I don't understand the question. Do you actually want to remove the min/max field values from the database (after the fields are already in the database), or do you want to drop the min/max values during processing after reading in the values (so that the fields stay in the database), or do you want to drop the min/max values during processing before you add them to the database?

[jmucchiello]

Try this:

select field1 + field2 + field3 + field4 + field5 + field6 - least(field1, field2, field3, field4, field5, field6) - greatest(field1, fiedl2, field3, field4, field5, field6) from ...

Of course, this only really works if you want to sum the 4 remaining fields.