So, I had an interesting problem crop up a little while ago. If you are storing a bitwise “flags” field in your database, and you need to order by a particular flag or flags of that field, how do you do it ?
It turns out the answer is relatively simple.
Table Structure: id, name, flags
Flag Values: 1 = Enemy, 2 = Friend, 4 = Owe Money To, 8 = Owes Me Money
Sample Data:
1, Matthew, 2 // Friend
2, Josh, 6 // Friend, I Owe Money
3, Thomas, 10 // Friend, Owes Me Money
4, John, 9 // Enemy, Owes Me Money
5, Abe, 2
6, Becky, 6
7, Jimmy, 10
8, Jason, 9
9, Dean, 1
10,Joseph, 5
11,Frank, 6
12,Julie, 11 // Frenemy, Owes me Money
13,Hannah, 10
14,Sam, 5
15,Q, 9
So we have a list of people in the database that are fall under a few different categories [flags] as referenced above. Now I want to query from this table to find out various things about the people contained within.
Now I want to see all of the people in the table that are my friends, ordered by those that owe me money, followed by those that I owe money to, followed by any others.
SQL:
SELECT *, (flags & 4 = 4) AS owesme, (flags & 8 = 8) AS iowethem FROM temp WHERE (flags & 2 = 2) ORDER BY owesme DESC, iowethem DESC, name
RESULT: 
I’m pretty happy with that. I had a need, and figured out how to scratch it. I am a little surprised that I had never needed this before, but it certainly does work! I hope this helps those that might be trying to Google for this.. I did not find a single entry, luckily my hunch paid off. I wonder if not a lot of people do this sort of thing ? I love storing flags, and now I can use those for easy ordering as well.
VistaDB Rocks:
I wasn’t completely sure that I was going to be able to pull this off in VisatDB, but sure enough, it worked like a charm and the first time. I’m quite impressed! Yet something else that VistaDB does well. I also tried it in MySQL to make sure I wasn’t on drugs either.
Experiment, play around with it, see if you can find a use for something like this.
Happy programming!
Matthew MacSuga
Leave a comment