Well, I felt clever anyways
I pulled off some SQL voodoo the other day that I thought was pretty neat. I don't know, maybe this is obvious to someone with more database experience than I have, but I thought I would write it down so I remember it later. The problem, in simplified form, is something like this:
I have a table with one column consisting of values, and another column consisting of specific sort orders for those values:
ValueOrder
Foo2
Bar4
Baz1
Qux3
Want I want to do is rewrite the order column so that orders correspond to the alphabetic orders of the values. So the end result should be:
ValueOrdering
Foo3
Bar1
Baz2
Qux4
Sure I could do this in my control code with a SELECT, sort, then a series of UPDATEs, but I knew there had to be a clever way to do this with a single SQL statement, even if it's ugly. This is the basic idea (it also assumes that your SQL implementation has a "REPLACE" statement or something similar, though something similar could be achieved with an UPDATE):
REPLACE INTO Vals SELECT v1.Value, COUNT(*)
FROM Vals v1 JOIN Vals v2 ON v1.Value >= v2.Value
GROUP BY v1.Value;
The idea is that for each Value, a JOIN is made on that value for each Value greater than or equal to it, so there will be one row for Bar, since no value is less than it; two rows for Baz since it is equal to itself and greater than Bar; and so on. Then just GROUP BY the values and COUNT the rows in each group, and you get the sort order. In practice this looks a bit uglier as there are more columns involved and CASTs and such, but it's the same basic idea.
I have a table with one column consisting of values, and another column consisting of specific sort orders for those values:
ValueOrder
Foo2
Bar4
Baz1
Qux3
Want I want to do is rewrite the order column so that orders correspond to the alphabetic orders of the values. So the end result should be:
ValueOrdering
Foo3
Bar1
Baz2
Qux4
Sure I could do this in my control code with a SELECT, sort, then a series of UPDATEs, but I knew there had to be a clever way to do this with a single SQL statement, even if it's ugly. This is the basic idea (it also assumes that your SQL implementation has a "REPLACE" statement or something similar, though something similar could be achieved with an UPDATE):
REPLACE INTO Vals SELECT v1.Value, COUNT(*)
FROM Vals v1 JOIN Vals v2 ON v1.Value >= v2.Value
GROUP BY v1.Value;
The idea is that for each Value, a JOIN is made on that value for each Value greater than or equal to it, so there will be one row for Bar, since no value is less than it; two rows for Baz since it is equal to itself and greater than Bar; and so on. Then just GROUP BY the values and COUNT the rows in each group, and you get the sort order. In practice this looks a bit uglier as there are more columns involved and CASTs and such, but it's the same basic idea.