ORDER BY with special order scheme?

Is it possible to order the result by a given list? E.g. I have a "type"
column and I want to return first the records with type 5, then with type
9, then with type 2 etc.

--
Frank Buss, fb@frank-buss.de
http://www.frank-buss.de, http://www.it4-systems.de

On 27 Jul, 08:21, Frank Buss <f...@frank-buss.de> wrote:
> Is it possible to order the result by a given list? E.g. I have a "type"
> column and I want to return first the records with type 5, then with type
> 9, then with type 2 etc.
>
> --
> Frank Buss, f...@frank-buss.dehttp://www.frank-buss.de,http://www.it4-systems.de

see FIELD

strawberry wrote:

> see FIELD

Thanks, this works. Now I have the problem that the elements which are not
in the FIELD array are showed first, e.g. select type from foo order by
field(type, 99, 1, 88)

0
2
99
99
99
1
1
88

Is it possible that all the elements which are not in the field array are
ordered at the end of the other rows?

--
Frank Buss, fb@frank-buss.de
http://www.frank-buss.de, http://www.it4-systems.de

On 27 Jul, 08:41, Frank Buss <f...@frank-buss.de> wrote:
> strawberry wrote:
> > see FIELD
>
> Thanks, this works. Now I have the problem that the elements which are not
> in the FIELD array are showed first, e.g. select type from foo order by
> field(type, 99, 1, 88)
>
> 0
> 2
> 99
> 99
> 99
> 1
> 1
> 88
>
> Is it possible that all the elements which are not in the field array are
> ordered at the end of the other rows?
>
> --
> Frank Buss, f...@frank-buss.dehttp://www.frank-buss.de,http://www.it4-systems.de

There are a couple of ways I would do this. Before I choose which one
to advise, a couple of questions:
How big is the field array likely to be?
Are the contents of the field array likely to change often?

Captain Paralytic wrote:

> There are a couple of ways I would do this. Before I choose which one
> to advise, a couple of questions:
> How big is the field array likely to be?

It is small, maybe 5 entries.

> Are the contents of the field array likely to change often?

No.

--
Frank Buss, fb@frank-buss.de
http://www.frank-buss.de, http://www.it4-systems.de

On 27 Jul, 09:53, Frank Buss <f...@frank-buss.de> wrote:
> Captain Paralytic wrote:
> > There are a couple of ways I would do this. Before I choose which one
> > to advise, a couple of questions:
> > How big is the field array likely to be?
>
> It is small, maybe 5 entries.
>
> > Are the contents of the field array likely to change often?
>
> No.
>
> --
> Frank Buss, f...@frank-buss.dehttp://www.frank-buss.de,http://www.it4-systems.de

I was about to advise you to use a case statement, but then I realised
that it is even easier than that.

The FIELD() function returns 0 if the string is not found. So just do
this:

SELECT
`type`
FROM `foo`
ORDER BY FIELD(type, 88, 1, 99) DESC