problems ordering result by paramters

Hi!
I have a table:

+-------------+---------------------+------+-----+---------+
| Field | Type | Null | Key | Default | |
+-------------+---------------------+------+-----+---------+
| eintrag | int(10) unsigned | NO | PRI | NULL |
auto_increment |
| aktivieren | tinyint(1) | NO | | 1 |
|
| name | varchar(100) | NO | | NULL |
|
| email | varchar(60) | NO | | NULL |
|
| betreff | varchar(100) | NO | | NULL |
|
| inhalt | text | NO | | NULL |
|
| sprache | tinyint(3) unsigned | NO | MUL | NULL |
|
| zeiteintrag | datetime | NO | | NULL |
|
| counter | int(10) unsigned | NO | | 0 |
|
+-------------+---------------------+------+-----+---------+

I want to order the results by language (column sprache).

select
eintrag,name,betreff,CONCAT(DAY(zeiteintrag),'.',M ONTH(zeiteintrag),'.',YEAR(zeiteintrag))
AS zeit,counter from guestbook where sprache in (2,1,3,4,5)"

After the where clause you see the column "sprache". I want to get the
output ordered in the order 2,1,3,4,5

But the output doesn't make it. What did I make wrong?


For any help and support, thank you. for any help, thank you in advance!


Tamer

>I want to order the results by language (column sprache).
>
>select
>eintrag,name,betreff,CONCAT(DAY(zeiteintrag),'.', MONTH(zeiteintrag),'.',YEAR(zeiteintrag))
>AS zeit,counter from guestbook where sprache in (2,1,3,4,5)"
>
>After the where clause you see the column "sprache". I want to get the
>output ordered in the order 2,1,3,4,5

If you don't use ORDER BY, you have absolutely no complaint about
what order the results come in. "in (2,1,3,4,5)" and "in (5,4,3,2,1)"
are equivalent. Neither say anything whatever about ordering.

>But the output doesn't make it. What did I make wrong?

Use ORDER BY. Make up an expression to return a value indicating
what order things should go in, or use a table. For example,
you might have a table `ordering`:

sprache ordering
2 1
1 2
3 3
4 4
5 5

join with `ordering` on ordering.sprache = guestbook.sprache, and then
ORDER BY ordering.ordering .

Another approach is to use a big complicated expression with CASE that
translates from sprache into some number that can be sorted on.

On Wed, 09 Jan 2008 02:10:39 +0100, Tamer Higazi <no@mail.de> wrote:

> Hi!
> I have a table:
>
> +-------------+---------------------+------+-----+---------+
> | Field | Type | Null | Key | Default | |
> +-------------+---------------------+------+-----+---------+
> | eintrag | int(10) unsigned | NO | PRI | NULL |
> auto_increment |
> | aktivieren | tinyint(1) | NO | | 1 |
> |
> | name | varchar(100) | NO | | NULL |
> |
> | email | varchar(60) | NO | | NULL |
> |
> | betreff | varchar(100) | NO | | NULL |
> |
> | inhalt | text | NO | | NULL |
> |
> | sprache | tinyint(3) unsigned | NO | MUL | NULL |
> |
> | zeiteintrag | datetime | NO | | NULL |
> |
> | counter | int(10) unsigned | NO | | 0 |
> |
> +-------------+---------------------+------+-----+---------+
>
> I want to order the results by language (column sprache).
>
> select
> eintrag,name,betreff,CONCAT(DAY(zeiteintrag),'.',M ONTH(zeiteintrag),'.',YEAR(zeiteintrag))
> AS zeit,counter from guestbook where sprache in (2,1,3,4,5)"
>
> After the where clause you see the column "sprache". I want to get the
> output ordered in the order 2,1,3,4,5
>
> But the output doesn't make it. What did I make wrong?

As stated, this is not how it works.
You could create a temporary table with the numbers + a sortorder field,
and join to that.
--
Rik Wasmus

Neither of all....

select
eintrag,name,betreff,CONCAT(DAY(zeiteintrag),'.',M ONTH(zeiteintrag),'.',YEAR(zeiteintrag))
AS zeit,counter from guestbook where sprache in (2,1,3,4,5) ORDER BY
FIND_IN_SET(sprache,'2,1,3,4,5');




Tamer Higazi wrote:
> Hi!
> I have a table:
>
> +-------------+---------------------+------+-----+---------+
> | Field | Type | Null | Key | Default | |
> +-------------+---------------------+------+-----+---------+
> | eintrag | int(10) unsigned | NO | PRI | NULL |
> auto_increment |
> | aktivieren | tinyint(1) | NO | | 1 |
> |
> | name | varchar(100) | NO | | NULL |
> |
> | email | varchar(60) | NO | | NULL |
> |
> | betreff | varchar(100) | NO | | NULL |
> |
> | inhalt | text | NO | | NULL |
> |
> | sprache | tinyint(3) unsigned | NO | MUL | NULL |
> |
> | zeiteintrag | datetime | NO | | NULL |
> |
> | counter | int(10) unsigned | NO | | 0 |
> |
> +-------------+---------------------+------+-----+---------+
>
> I want to order the results by language (column sprache).
>
> select
> eintrag,name,betreff,CONCAT(DAY(zeiteintrag),'.',M ONTH(zeiteintrag),'.',YEAR(zeiteintrag))
> AS zeit,counter from guestbook where sprache in (2,1,3,4,5)"
>
> After the where clause you see the column "sprache". I want to get the
> output ordered in the order 2,1,3,4,5
>
> But the output doesn't make it. What did I make wrong?
>
>
> For any help and support, thank you. for any help, thank you in advance!
>
>
> Tamer