Re: insert ... select .. order by problem - Sinisa Milivojevic - com.mysql.lists.bugs

7 messages in com.mysql.lists.bugsRe: insert ... select .. order by pr...

From	Sent On	Attachments
Alejandro Paz	01 Sep 2003 08:07
Sinisa Milivojevic	01 Sep 2003 10:07
Alejandro Paz	01 Sep 2003 23:53
Sergei Golubchik	02 Sep 2003 02:50
Alejandro Paz	02 Sep 2003 02:58
Sinisa Milivojevic	02 Sep 2003 04:03
Sergei Golubchik	02 Sep 2003 06:37

Subject:	Re: insert ... select .. order by problem
From:	Sinisa Milivojevic (sin...@mysql.com)
Date:	09/02/2003 04:03:46 AM
List:	com.mysql.lists.bugs

Alejandro Paz writes:

Hi,

I use ORDER BY, because I want that order in PTemp table, so I do not have to order them later (because they are retrieved several times later).

1. Inserting with mysql c.l.i. :

I get the records well sorted : first by a, secondly by b and finally by c (ascendig order). I'm using d to relink both tables in a join.

select a,b,c from Stock,PTable where Stock.d=PTable.d;

2. Inserting with the application :

I get the records badly sorted : first by a, secondly by b and finally by c, but in descending order. As all records has the same value in a, so the records that in case 1 start with '0' are at the beginning, the same records here are at the end. I'm using d to relink both tables in a join.

select a,b,c from Stock,PTable where Stock.d=PTable.d;

I thought that was clear when I said reverse order, sorry.

Hi!

The order of retrieval of rows does not necessary has to be the same as the row order in the table.

You should get the same result with mysql cli and in application.

Try using ..store_result instead of ..use_result function.

Still, no order is guaranteed if your SELECT does not use GROUP BY or ORDER BY.

Last but not least, if a,b,c are present in both tables, you should specify a table in the select list.

Sincerely,