Re: Lack of "real" example makes it very dificult to understand!

[Nick Roper]

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