Organizing a result set a specific way.
Posted: Thu Apr 02, 2009 6:47 pm
In MySQL, is there a way to define the organization or order of a result set specifically? I know that I could order a result set alphabetically using the ORDER BY clause, but can I define a specific order? For example, let's say I have a table with a column called `name` which contains names of people. I could write ORDER BY `name` and it will order the result set alphabetically based on the column `name`. But is there a way to define the ordering like: ORDER BY ("John", "Marry", "Neil Armstrong", "Jesus Christ", "Wall-e", ...), or something?
I'm trying to order my result set, which has very few rows (about six maybe), and I know the names of the fields and I'd like to arrange the names so that way when I loop though the result set I get a specific, predefined, order.
If you don't know of a way to do exactly what I'm saying, then anything on ordering mysql result sets would be good.
Thanks for reading. All help is appreciated on this.
[EDIT] I think I found what I'm looking for, although I don't know quite how it works:
Could someone please explain to me how this works?
[EDIT] I see how this works now. Due to a little bit of testing it seems the FIELD function returns an index number of the listed arguments. It takes the first argument and matches it with the ones after it, when it finds a match it returns the index of that match. So for example:
The first parameter is the string 'foo', the second is 'bar', and the third is 'foo' again. Sense 'foo' is the first parameter, FIELD() goes through the rest of the parameters until it finds a match, which it does at the third parameter. The third parameter is also the 2nd index (because indexes start at 0), so it returns 2.
If FIELD() doesn't find a match it returns 0. Also, if the first parameter can be a column name
So I think I understand how this works now. Is there anything else I missing, or that I need to know?
[EDIT] Is there a better way to do this:
???
I'm trying to order my result set, which has very few rows (about six maybe), and I know the names of the fields and I'd like to arrange the names so that way when I loop though the result set I get a specific, predefined, order.
If you don't know of a way to do exactly what I'm saying, then anything on ordering mysql result sets would be good.
Thanks for reading. All help is appreciated on this.
[EDIT] I think I found what I'm looking for, although I don't know quite how it works:
Code: Select all
... ORDER BY FIELDS(name, 'Joe', 'Bob', etc.)[EDIT] I see how this works now. Due to a little bit of testing it seems the FIELD function returns an index number of the listed arguments. It takes the first argument and matches it with the ones after it, when it finds a match it returns the index of that match. So for example:
Code: Select all
FIELD('foo', 'bar', 'foo')If FIELD() doesn't find a match it returns 0. Also, if the first parameter can be a column name
So I think I understand how this works now. Is there anything else I missing, or that I need to know?
[EDIT] Is there a better way to do this:
Code: Select all
SELECT category_id, name, type,
FIELD(type REGEXP '^', type REGEXP 'Community|Pets', type REGEXP 'For Sale', type REGEXP 'Housing|Services', type REGEXP 'Jobs') AS `GROUP`
FROM categories
ORDER BY `GROUP`, `type`, `name`