Select AS with negative numbers (another lousy topic title)


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.


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.


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.


Then I would tinker with casting the values to signed.

cast(column as signed)