Expression for "CLOSEST TO XX"

[grindange]

I have quiz on a website, it's 20 question with three alternative and one elimination question. The answer on the elimation question is a value between 1 and 50.

The database who is connected to the website have the columns "name", "points" and "elimination"

I need a SQL-statment to order the the competitor who have the same points. The one who is closest to the the elimination answer should be first.

E.g: Two competitor have 20 points, one of them have answered 15 an the other 25 on the elimination question. The correct answer is 17, so 15 is closest and he should be number one.

Anyone who can help me!

 

[towerbase]

If you multiply the points by, say, 100 and then subtract the absolute value of (the elimination score minus the elimination answer) you will select your contenstants in the way you want.

So something like this should work

SELECT * FROM table ORDER BY points * 100 - ABS(elimination-17) DESC

Andrew
Hampshire, UK