Generic SQL and custom sort orders

[jambuie]

You could use a CASE statement to do this.

E.g:

CASE WHEN manufacturer = 'Ford' THEN 1
WHEN manufacturer ='Toyota' THEN 2
WHEN manufacturer ='Vauxhall' THEN 3
WHEN manufacturer ='Mazda' THEN 4
WHEN manufacturer ='Saab' THEN 5
WHEN manufacturer ='Porsche' THEN 6
WHEN manufacturer ='Mercedes' THEN 7
WHEN manufacturer ='Suzuki' THEN 8
WHEN manufacturer ='Honda' THEN 9
WHEN manufacturer ='BMW' THEN 10
END as sort_order

and then sort on that calculation.

[expat]

Not using Oracle. Solution needs to be vendor independant (i.e. SQL92 std).

Updates are infrequent and data reads are very high. There will be about 1000 items in the table and they are presented to the user 10 at a time.

At that rate* I'd take a brute-force approach and just update the entire table's sequence-numbers every time you move an item. And index on the sequence-number.

* i.e. few updates so you can stand it being slow; only 1000 items so an update won't be that slow in human terms; reads are high so index on a column will be the fastest way to do that.

PS and eliminate any indexes that you don't need, if any: no need to update them every time.

[PerlOfWisdom]

Number the sort order column in even numbers, then you can move an item between two others by using an odd number. You only need to renumber between the movedFrom and movedTo records.