Recently a colleague of mine was working on bringing new functionality to a piece of legacy code.

The legacy code was referencing a cached image in a rather peculiar manner in that it pulled a set of results back from the database then built up the filename using the index position taken from a for loop as folows:

Strangly enough, the results never varied so the approach used was actually adequate, however the new functionality introduced an element of randomness which whilst in principle the concept worked, for the new functionality to work correctly, this approach was completely useless.

The new functionality requries an index to be added which should be reset each time the category ID changes. This means that should the category ID alter from 7 to 8, the index position should be reset to 1.

This can easily be carried out within PHP by backreferencing the category ID as:

This approach would be fine, however can this be achieved in pure SQL?

The answer is yes by using a nested statement on a variable assignment within the select:

This uses a count of category ID as a modulus on itself allowing the index to be reset to 1 each time the category ID changes.

catid imageid index
8 49 1
8 46 2
8 47 3
8 48 4
9 54 1
9 50 2
9 51 3
9 52 4
9 53 5
10 56 1
10 55 2
11 59 1
11 57 2
11 58 3
12 62 1
11 60 2

Leave a Comment