order by problem

In a mysql database is a field called 'number' which is actually
filled by numbers. In a query I want my results ordered by number so
the last clause in my query is ORDER BY 'number'. Numbers then are
given in an 'alphabetical'order: first 100 and later e.g. 83 etc. Is
it possible to get numbers like this in such a way that in a sequence
from 1 to 111 the 1 comes first and 111 last? Ordering by id or other
field is no option.
The number field is INT(3).
Thank you,
Onno

On 2008-01-12, Jannis <anne.bosscha@hccnet.nl> wrote:
> In a mysql database is a field called 'number' which is actually
> filled by numbers. In a query I want my results ordered by number so
> the last clause in my query is ORDER BY 'number'. Numbers then are
> given in an 'alphabetical'order: first 100 and later e.g. 83 etc. Is
> it possible to get numbers like this in such a way that in a sequence
> from 1 to 111 the 1 comes first and 111 last? Ordering by id or other
> field is no option.
> The number field is INT(3).
> Thank you,
> Onno

Works for me. I think you are using 'apostrophes'
where you intended `backticks`.

mysql> create table foo (`number` int(3));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into foo set `number` = 10;
Query OK, 1 row affected (0.02 sec)

mysql> insert into foo set `number` = 111;
Query OK, 1 row affected (0.01 sec)

mysql> insert into foo set `number` = 1;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM foo;
+--------+
| number |
+--------+
| 10 |
| 111 |
| 1 |
+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM foo ORDER BY `number`;
+--------+
| number |
+--------+
| 1 |
| 10 |
| 111 |
+--------+
3 rows in set (0.00 sec)

Charles Polisher wrote:
> On 2008-01-12, Jannis <anne.bosscha@hccnet.nl> wrote:
>> In a mysql database is a field called 'number' which is actually
>> filled by numbers. In a query I want my results ordered by number so
>> the last clause in my query is ORDER BY 'number'. Numbers then are
>> given in an 'alphabetical'order: first 100 and later e.g. 83 etc. Is
>> it possible to get numbers like this in such a way that in a sequence
>> from 1 to 111 the 1 comes first and 111 last? Ordering by id or other
>> field is no option.
>> The number field is INT(3).
>> Thank you,
>> Onno
>
> Works for me. I think you are using 'apostrophes'
> where you intended `backticks`.
>
> mysql> create table foo (`number` int(3));
> Query OK, 0 rows affected (0.04 sec)
>
> mysql> insert into foo set `number` = 10;
> Query OK, 1 row affected (0.02 sec)
>
> mysql> insert into foo set `number` = 111;
> Query OK, 1 row affected (0.01 sec)
>
> mysql> insert into foo set `number` = 1;
> Query OK, 1 row affected (0.01 sec)
>
> mysql> SELECT * FROM foo;
> +--------+
> | number |
> +--------+
> | 10 |
> | 111 |
> | 1 |
> +--------+
> 3 rows in set (0.00 sec)
>
> mysql> SELECT * FROM foo ORDER BY `number`;
> +--------+
> | number |
> +--------+
> | 1 |
> | 10 |
> | 111 |
> +--------+
> 3 rows in set (0.00 sec)
>
>

you should never define a column name using reserved key words - they
only cause you more grief than they are worth!

On Sat, 12 Jan 2008 21:23:38 -0000 wrote Charles Polisher
<cpolish@nonesuch.com>:

>On 2008-01-12, Jannis <anne.bosscha@hccnet.nl> wrote:
>> In a mysql database is a field called 'number' which is actually
>> filled by numbers. In a query I want my results ordered by number so
>> the last clause in my query is ORDER BY 'number'. Numbers then are
>> given in an 'alphabetical'order: first 100 and later e.g. 83 etc. Is
>> it possible to get numbers like this in such a way that in a sequence
>> from 1 to 111 the 1 comes first and 111 last? Ordering by id or other
>> field is no option.
>> The number field is INT(3).
>> Thank you,
>> Onno
>
>Works for me. I think you are using 'apostrophes'
>where you intended `backticks`.
>
>mysql> create table foo (`number` int(3));
>Query OK, 0 rows affected (0.04 sec)
>
>mysql> insert into foo set `number` = 10;
>Query OK, 1 row affected (0.02 sec)
>
>mysql> insert into foo set `number` = 111;
>Query OK, 1 row affected (0.01 sec)
>
>mysql> insert into foo set `number` = 1;
>Query OK, 1 row affected (0.01 sec)
>
>mysql> SELECT * FROM foo;
>+--------+
>| number |
>+--------+
>| 10 |
>| 111 |
>| 1 |
>+--------+
>3 rows in set (0.00 sec)
>
>mysql> SELECT * FROM foo ORDER BY `number`;
>+--------+
>| number |
>+--------+
>| 1 |
>| 10 |
>| 111 |
>+--------+
>3 rows in set (0.00 sec)
>

Actually I used those `backticks`. Sorry about that. Still 2 comes
after 111.

Jannis wrote:
>
> Actually I used those `backticks`. Sorry about that. Still 2 comes
> after 111.

So export the CREATE TABLE statement and a few sample rows of data from
phpMyAdmin and post them here for us to take a look at.

On Sun, 13 Jan 2008 13:36:20 -0000 wrote "Paul Lautman"
<paul.lautman@btinternet.com>:

>Jannis wrote:
>>
>> Actually I used those `backticks`. Sorry about that. Still 2 comes
>> after 111.
>
>So export the CREATE TABLE statement and a few sample rows of data from
>phpMyAdmin and post them here for us to take a look at.
>
>
>
The system works as long as I'm working in mySQL. After copying tot
PHP the problem occurs. I did not expect this. Maybe I posted this in
the wrong group. Sorry.
As for using names. Actually I gave the fields names in Dutch, so
there are no problems about reserved terms.

On 14 Jan, 10:49, Jannis <anne.boss...@hccnet.nl> wrote:
> On Sun, 13 Jan 2008 13:36:20 -0000 wrote "Paul Lautman"
> <paul.laut...@btinternet.com>:
>
> >Jannis wrote:
>
> >> Actually I used those `backticks`. Sorry about that. Still 2 comes
> >> after 111.
>
> >So export the CREATE TABLE statement and a few sample rows of data from
> >phpMyAdmin and post them here for us to take a look at.
>
> The system works as long as I'm working in mySQL. After copying tot
> PHP the problem occurs. I did not expect this. Maybe I posted this in
> the wrong group. Sorry.
> As for using names. Actually I gave the fields names in Dutch, so
> there are no problems about reserved terms.

I don't understand what you mean by "copying tot (SIC) PHP"

If you are running a query, you are using MySQL.

On Mon, 14 Jan 2008 03:13:46 -0800 (PST) wrote Captain Paralytic
<paul_lautman@yahoo.com>:

>On 14 Jan, 10:49, Jannis <anne.boss...@hccnet.nl> wrote:
>> On Sun, 13 Jan 2008 13:36:20 -0000 wrote "Paul Lautman"
>> <paul.laut...@btinternet.com>:
>>
>> >Jannis wrote:
>>
>> >> Actually I used those `backticks`. Sorry about that. Still 2 comes
>> >> after 111.
>>
>> >So export the CREATE TABLE statement and a few sample rows of data from
>> >phpMyAdmin and post them here for us to take a look at.
>>
>> The system works as long as I'm working in mySQL. After copying tot
>> PHP the problem occurs. I did not expect this. Maybe I posted this in
>> the wrong group. Sorry.
>> As for using names. Actually I gave the fields names in Dutch, so
>> there are no problems about reserved terms.
>
>I don't understand what you mean by "copying tot (SIC) PHP"
>
>If you are running a query, you are using MySQL.

I mean
<?php $sql="(the sql query)"; ?>
and of course what is needed more to read the results.

On Mon, 14 Jan 2008 12:49:33 +0100, Jannis <anne.bosscha@hccnet.nl> wrote:
> On Mon, 14 Jan 2008 03:13:46 -0800 (PST) wrote Captain Paralytic
> <paul_lautman@yahoo.com>:
>> On 14 Jan, 10:49, Jannis <anne.boss...@hccnet.nl> wrote:
>>> The system works as long as I'm working in mySQL. After copying tot
>>> PHP the problem occurs. I did not expect this. Maybe I posted this in
>>> the wrong group. Sorry.

>> If you are running a query, you are using MySQL.

> I mean
> <?php $sql="(the sql query)"; ?>
> and of course what is needed more to read the results.

Simply put: if the query works in MySQL, then it will work identically if
either PHP or any other client sends it to MySQL. Ergo: your query in PHP
is NOT exactly the same, so print/echo it out, and find the difference.
--
Rik Wasmus

On Mon, 14 Jan 2008 12:57:45 +0100 wrote "Rik Wasmus"
<luiheidsgoeroe@hotmail.com>:

>On Mon, 14 Jan 2008 12:49:33 +0100, Jannis <anne.bosscha@hccnet.nl> wrote:
>> On Mon, 14 Jan 2008 03:13:46 -0800 (PST) wrote Captain Paralytic
>> <paul_lautman@yahoo.com>:
>>> On 14 Jan, 10:49, Jannis <anne.boss...@hccnet.nl> wrote:
>>>> The system works as long as I'm working in mySQL. After copying tot
>>>> PHP the problem occurs. I did not expect this. Maybe I posted this in
>>>> the wrong group. Sorry.
>
>>> If you are running a query, you are using MySQL.
>
>> I mean
>> <?php $sql="(the sql query)"; ?>
>> and of course what is needed more to read the results.
>
>Simply put: if the query works in MySQL, then it will work identically if
>either PHP or any other client sends it to MySQL. Ergo: your query in PHP
>is NOT exactly the same, so print/echo it out, and find the difference.

I did so. The difference is that I concatenated two columns in order
to get e.g. 3 and 3A in the same column. I expected the same behavior
for the new column, but I forgot to test that. As you know this new
column has to get a new name and this is ordered alphabetically even
in SQL.
For me it is a surprise, but maybe that is lack of experience.
Thank for your help.
Jannis