Category Archives: MySQL

MySQL: From Differences to Gains and Losses

The problem with blogging solutions specifically for one person is that he comes back and asks for more 😉 Luckily this time it’s easier!

What we got: a simple table with one column whose values describe a difference of some kind. That is we got one column called difference, that has negative or positive (or 0) numbers.
What we want: we’re looking for the most simple and performant way to update two other columns from that difference one: if the difference was positive, this value is to be updated into a gain column. If it is negative, then the positive value (the absolute value) is to be updated into the loss column.

Read more »

Rankings in MySQL – global ranks and local ranks within subsets

I was asked by someone to help him figuring out how to calculate and store rankings in an SQL relation. There are already some resources on the web that show how to calculate rankings with MySQL but the specific problem today has two more facets to it:

  • don’t calculate a single rank of one item but update all ranks of all items in one query
  • not only calculate the global rank of each item but also the rank of each item within some subset it is part of

Read more »