Re: Strange ORDER BY question(SOLUTION)
* Mike Johnson > From: Lewis, Jason [mailto:[EMAIL PROTECTED] > > > Didn't know if anyone else might need this but I was given > > the solution. > > > > SELECT * FROM tablename > > ORDER BY MemberLevel='Platinum' DESC, > > MemberLevel='Gold' DESC, > > MemberLevel='Silver' DESC, > > MemberLevel='Paying' DESC, > > MemberLevel='Non-Paying' DESC; > > > > > > Thanks again Mike! > > > Thanks for posting it, actually, as I wasn't aware of that > solution until your question prompted me to go looking. I think using the FIELD() function is the easiest way: SELECT * FROM tablename ORDER BY FIELD(MemberLevel, 'Platinum','Gold','Silver','Paying','Non-Paying'); http://www.mysql.com/doc/en/String_functions.html#IDX1261 > -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Strange ORDER BY question(SOLUTION)
From: Lewis, Jason [mailto:[EMAIL PROTECTED] > Didn't know if anyone else might need this but I was given > the solution. > > SELECT * FROM tablename > ORDER BY MemberLevel='Platinum' DESC, > MemberLevel='Gold' DESC, > MemberLevel='Silver' DESC, > MemberLevel='Paying' DESC, > MemberLevel='Non-Paying' DESC; > > > Thanks again Mike! Thanks for posting it, actually, as I wasn't aware of that solution until your question prompted me to go looking. -- 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]
RE: Strange ORDER BY question(SOLUTION)
Didn't know if anyone else might need this but I was given the solution. SELECT * FROM tablename ORDER BY MemberLevel='Platinum' DESC, MemberLevel='Gold' DESC, MemberLevel='Silver' DESC, MemberLevel='Paying' DESC, MemberLevel='Non-Paying' DESC; Thanks again Mike! Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange ORDER BY question
On Mon, 12 Jan 2004, Lewis, Jason wrote:
> 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.
>
Can think of 3 options offhand.
1. ENUM() and order by enumcol, this will order them in the order you
specify them in the ENUM('col1', ...) order.
2. Break it out into another table andgive them ids in the order they
should be sorted
3. use a CASE statement:
SELECT col, CASE col WHEN 'Platinum' THEN 1 WHEN 'Gold' THEN 2 ... END AS
sortby ORDER BY sortby
cheers,
Tobias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Strange ORDER BY question
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]
Strange ORDER BY question
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. Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
