Organizing a result set a specific way.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Organizing a result set a specific way.

Post by JellyFish »

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:

Code: Select all

... ORDER BY FIELDS(name, 'Joe', 'Bob', etc.)
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:

Code: Select all

FIELD('foo', 'bar', 'foo')
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:

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`
???
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Organizing a result set a specific way.

Post by VladSun »

I think a better idea is to add an additional column (e.g. `position`) where you define the result ordering.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply