RE: Strange ORDER BY question

2004-01-12 Thread Mike Johnson 
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]

Re: Strange ORDER BY question

2004-01-12 Thread Tobias Asplund 
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(SOLUTION)

2004-01-12 Thread Lewis, Jason 

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(SOLUTION)

2004-01-12 Thread 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.


-- 
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)

2004-01-12 Thread Roger Baklund 
* 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');

URL: 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]