Select AS with negative numbers (another lousy topic title)

[C_Bob]

I have a query that looks like this:
SELECT ROUND((wins/matches * (elo - 2000))) as rank FROM kittens ORDER BY elo ASC LIMIT 1

In phpmyadmin, it constantly returns #1690 - BIGINT UNSIGNED value is out of range. The corresponding query with DESC instead of ASC works fine, so I assume it’s choking on the negative numbers.

I’ve looked all around and I can’t see anything anywhere about a way to make these “temporary columns” signed. It doesn’t seem like it can be this hard. If anyone knows how to get this right I’d really appreciate their insight.

[astonecipher]

What kind of numbers are you dealing with? you could always cast the value, but it would be better to know which value you need to cast, first.

[C_Bob]

The intent is to take an Elo rating (which is an unsigned smallint(5) ) and adjust them slightly by win rates, which are derived from two more unsigned smallint(5)s, wins and matches, to come up with a sort of adjusted secondary rank – intended to try to help provide a slightly more accurate ranking for kittens with few matches than others by accounting for their current winning percentage.

2000 is the default value that a kitten is given when it enters the system. I subtract it from the Elo so that the number being used for the percentage-based adjustment is only the amount of difference. The resulting difference could result in a range of up to 4 digits, either a positive or negative number, although it will normally fall into 3 or less.

I hope this explains my thought process, but if not, please ask again and I’ll try to do a better job of clarifying. Thanks so much for your time.

[astonecipher]

Then I would tinker with casting the values to signed.

cast(column as signed)