How to ORDER BY a Bitwise Field in SQL

| No Comments | No TrackBacks

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:
image

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

No TrackBacks

TrackBack URL: http://www.csharpbydesign.com/cgi-bin/mt/mt-tb.cgi/24

Leave a comment

About this Entry

This page contains a single entry by Matthew M. published on December 11, 2008 9:43 PM.

A Move to a New Host was the previous entry in this blog.

Core Programming Exercises is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.