MySQL Forums
Forum List  »  Docs

Re: Lack of "real" example makes it very dificult to understand!
Posted by: Nick Roper
Date: August 09, 2004 03:50AM

Hi V@no,

OK, here's a possible solution - based on the following table & data:

CREATE TABLE people (
name char(10) default NULL
)

INSERT INTO people VALUES ('Tom'),('Julie'),('Mike'),('Sue'),('V@no'),('Joe');

Then, suppose you always want the list to start with:

V@no
Sue
Mike
other names in order...

SELECT * FROM people
ORDER BY FIELD(name,'Mike','Sue','V@no') Desc, name ASC;

will give:

V@no
Sue
Mike
Joe
Julie
Tom

and

SELECT * FROM people
ORDER BY FIELD(name,'Mike','Sue','V@no') Desc, name DESC;

will give:

V@no
Sue
Mike
Tom
Julie
Joe

The FIELD function returns the ordinal value of an item found in the list, e.g. Mike->1, Sue->2, V@no-> and any other name will return 0. Applying a DESC order to this expression makes sure that values > 0 come first (although in the opposite sequence to that specified in the list), followed by all of the names which are assigned value of 0 and then sorted in ascending order of name.

You may need to play around with it for your requirements, but hopefully it will give you a start.

By the way - this is based on an example in the MySQL Cookbook by Paul Dubois - so, if it works for you, you might want to get the book.

Cheers,

Nick


--
Nick Roper

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Lack of "real" example makes it very dificult to understand!
2891
August 09, 2004 03:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.