From: Lewis, Jason [mailto:[EMAIL PROTECTED] > Okay I have a field in my db called MemberLevel in this field > you can be one of 5 levels. > > Platinum > Gold > Silver > Paying > Non-Paying > > > now my question is, how would I ORDER BY MemberLevel and get > it to come out in the above order? I have been racking my > brains for a week on this one and any help will be appreciated.
I just saw in the online manual comments that this can be done like this: SELECT * FROM tablename ORDER BY MemberLevel='Platinum' DESC, MemberLevel='Gold' DESC, MemberLevel='Silver' DESC, MemberLevel='Paying' DESC, MemberLevel='Non-Paying' DESC; (from http://www.mysql.com/doc/en/Sorting_rows.html) However, before I saw that, I'd have suggested a meta-table for these values, upon which you could sort by rank. Something such as... CREATE TABLE MemberLevels ( rank INT NOT NULL PRIMARY KEY, description VARCHAR(20) ); INSERT INTO MemberLevels (rank, description) VALUES (0, 'Platinum'); INSERT INTO MemberLevels (rank, description) VALUES (1, 'Gold'); INSERT INTO MemberLevels (rank, description) VALUES (2, 'Silver'); INSERT INTO MemberLevels (rank, description) VALUES (3, 'Paying'); INSERT INTO MemberLevels (rank, description) VALUES (4, 'Non-Paying'); Then remap the MemberLevel column in your table(s) to use the MemberLevels.rank values instead, possibly renaming the MemberLevel column to MemberLevelRank for clarity, and then join on that table in order to sort in their ranked order: SELECT tablename.*, MemberLevels.description FROM tablename, MemberLevels WHERE tablename.MemberLevelRank=MemberLevels.rank ORDER BY MemberLevels.rank ASC; As said, I imagine the previous solution would be better, but it never hurts to suggest another way. :) -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
