insert ... select .. order by, problem
Hallo everyone ! I already sent this, but I think some people think is not clear enough ;-) Im using Mysql 4.0.12 on RedHat 7.3 x86 I know it's not the last binary but I cannot upgrade now. (And i saw nothing about this in the changelog for 4.013 and 4.0.14) I found the following : I have two tables : Stock (InnoDB, primary key on d): a char (16) b char (20) c char (20) d int e decimal (9,2) h int i int PTemp (MyISAM, no keys): d int e decimal f int g char (1) And the statement I am using is : INSERT INTO PTemp SELECT d,e,32,'E' FROM Stock WHERE hi ORDER BY a,b,c; I am doing an insert/select with order by, in both cases I am using the same statemant. When I use the same statement in my application (built with C, and statically linked to libmysqlclient.a) I get the reversed order (the records that start with '0' are at the end). When I test the statement in the mysql cli and I get the results well sorted (the records that start with '0' are at the begining). I checked the log and both statements are equal, (but the two users used to access the DB are different, the mysql cli user is root, and the other just have enough permissions to select, update,delete and insert in the tables). I would like to know (if that is possible) what happens. Thanks in advance. Ale __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Hi, well, I'm not totally sure about your question. Which order is reversed ? The order you get the entries with a select after the insert ? If it is this, then I think it's not a problem with the insert. The order is then given by the select, and if no order by is in the select, it is arbitrary. Since the physical order of entries in your temp table isn't of any relevance, I can't see any point in using order by in this statement. Stefan Am Tuesday 02 September 2003 10:06 schrieb Alejandro Paz: Hallo everyone ! I already sent this, but I think some people think is not clear enough ;-) Im using Mysql 4.0.12 on RedHat 7.3 x86 I know it's not the last binary but I cannot upgrade now. (And i saw nothing about this in the changelog for 4.013 and 4.0.14) I found the following : I have two tables : Stock (InnoDB, primary key on d): a char (16) b char (20) c char (20) d int e decimal (9,2) h int i int PTemp (MyISAM, no keys): d int e decimal f int g char (1) And the statement I am using is : INSERT INTO PTemp SELECT d,e,32,'E' FROM Stock WHERE hi ORDER BY a,b,c; I am doing an insert/select with order by, in both cases I am using the same statemant. When I use the same statement in my application (built with C, and statically linked to libmysqlclient.a) I get the reversed order (the records that start with '0' are at the end). When I test the statement in the mysql cli and I get the results well sorted (the records that start with '0' are at the begining). I checked the log and both statements are equal, (but the two users used to access the DB are different, the mysql cli user is root, and the other just have enough permissions to select, update,delete and insert in the tables). I would like to know (if that is possible) what happens. Thanks in advance. Ale __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Hi Stephan, Let's see the case : 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. 2. Inserting with the application : I get the records well 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. I thought that was clear when I said reverse order, sorry. So you see what I mean ? thanks ! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Hi Alejondro 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). If I understand correct ,,, U can never trust the order ur records get return from DB (it is indepented of the order u insert records ) with other words , I u are interest in a specific order when selecting , u MUST use Order By hope this help regards Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Hi, I think you can't do this. There is no order in the table, so there is no point in using order by with insert. You always have to do this when retrieving the records (the order you get with select without order by is accidential). HTH Stefan Am Tuesday 02 September 2003 11:49 schrieb Alejandro Paz: Hi Stephan, Let's see the case : 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. 2. Inserting with the application : I get the records well 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. I thought that was clear when I said reverse order, sorry. So you see what I mean ? thanks ! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Stefan, Do you imply that tables cannot be sorted desc or asc based on one of the columns e.g. a last name? or am I misunderstanding you. Albert Atlanta (anyone else in Atlanta?) - Original Message - From: Stefan Kuhn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 6:57 AM Subject: Re: insert ... select .. order by, problem Hi, I think you can't do this. There is no order in the table, so there is no point in using order by with insert. You always have to do this when retrieving the records (the order you get with select without order by is accidential). HTH Stefan Am Tuesday 02 September 2003 11:49 schrieb Alejandro Paz: Hi Stephan, Let's see the case : 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. 2. Inserting with the application : I get the records well 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. I thought that was clear when I said reverse order, sorry. So you see what I mean ? thanks ! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Hi Albert, you are not misunderstanding me :-) Tables can indeed not be sorted, it's output which gets sorted. The difference is not academic, but important: It's not the table which gets an order, but the output. Take a command like: insert into x ... select from y ... order by z. Here the output of select gets sorted and inserted into the table x. In this table, there is not order, so if you do then a select from x, the order is arbitrary again and you need to do select from x order by z. And this means you could have dropped the order by in insert totally. Hope it became clear. Stefan Am Tuesday 02 September 2003 13:20 schrieb Albert: Stefan, Do you imply that tables cannot be sorted desc or asc based on one of the columns e.g. a last name? or am I misunderstanding you. Albert Atlanta (anyone else in Atlanta?) - Original Message - From: Stefan Kuhn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 6:57 AM Subject: Re: insert ... select .. order by, problem Hi, I think you can't do this. There is no order in the table, so there is no point in using order by with insert. You always have to do this when retrieving the records (the order you get with select without order by is accidential). HTH Stefan Am Tuesday 02 September 2003 11:49 schrieb Alejandro Paz: Hi Stephan, Let's see the case : 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. 2. Inserting with the application : I get the records well 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. I thought that was clear when I said reverse order, sorry. So you see what I mean ? thanks ! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
* Albert Stefan, I'm Roger, but I reply anyway. :) Do you imply that tables cannot be sorted desc or asc based on one of the columns e.g. a last name? or am I misunderstanding you. In relational database theory the order of rows within the table is undefined, i.e. it is up to the server, and the server can re-organize a table at any time. If you want an ordered result, you have to use ORDER BY in your SELECT statement. However, the MySQL server has some features that can be used to achieve exactly what you ask. It is possible to sort a table physically, in order to do faster reads later. The order is however destroyed if you do additional inserts. See the ORDER BY option of the ALTER TABLE statement: ORDER BY allows you to create the new table with the rows in a specific order. Note that the table will not remain in this order after inserts and deletes. In some cases, it may make sorting easier for MySQL if the table is in order by the column that you wish to order it by later. This option is mainly useful when you know that you are mostly going to query the rows in a certain order; by using this option after big changes to the table, you may be able to get higher performance. URL: http://www.mysql.com/doc/en/ALTER_TABLE.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Stefan, Indeed, and my mistake (semantics). I meant what you explained. It is clear to me that the order in the table remains in the manner the data were entered, and that cannot be changed, unless a record is deleted and then re-entered, which would place it elsewhere (at the end). This does not really have any benefits IMHO. And yes, I understand that it is the output that is sorted based on the query. Thanks for clarifying this, and as I read my question, I should have seen the difference myself. Mea culpa! : =) I am quite familiar with SQL Server 2000, but need to use MySQL for a project for the University I am at (Devry Alpharetta, Atlanta), to capture the input from a student survey of the classes and the Faculty members. There are several fields: semester (char), course(char), courseID (int), Faculty (varchar[30] - if that is acceptable in that format - and the answers to 18 questions, all alpha characters (char) or numeric char (int), and one Boolean (yes/no or 1,0). I need to figure out how to best structure this, e.g. create tables on the fly (if that is possible using ASP/ADO and SQL with ODBC connector), or create tables with many to many relationships and store the data for each course survey in a separate table. The tables with many to many relationships would hold all the courses, courseID's, and Faculty members, and the answers to the survey would create links between those and the results from the surveys. A typical class unique identification would look like this: sum03_FBaah_CIS_349 The cols would be 1 through 18 + a calculation col for the average of questions 1 to 18 and a col for the average of all answers to question 1, question 2, etc ... Mind you I may export the answers to an excel spreadsheet and do the calculations there rather than in the DB itself. Anyway this is a long answer to your response but I wanted those who read this to get an idea of what I am working with. Any suggestions are welcomed. Albert - Original Message - From: Stefan Kuhn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 8:10 AM Subject: Re: insert ... select .. order by, problem Hi Albert, you are not misunderstanding me :-) Tables can indeed not be sorted, it's output which gets sorted. The difference is not academic, but important: It's not the table which gets an order, but the output. Take a command like: insert into x ... select from y ... order by z. Here the output of select gets sorted and inserted into the table x. In this table, there is not order, so if you do then a select from x, the order is arbitrary again and you need to do select from x order by z. And this means you could have dropped the order by in insert totally. Hope it became clear. Stefan Am Tuesday 02 September 2003 13:20 schrieb Albert: Stefan, Do you imply that tables cannot be sorted desc or asc based on one of the columns e.g. a last name? or am I misunderstanding you. Albert Atlanta (anyone else in Atlanta?) - Original Message - From: Stefan Kuhn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 6:57 AM Subject: Re: insert ... select .. order by, problem Hi, I think you can't do this. There is no order in the table, so there is no point in using order by with insert. You always have to do this when retrieving the records (the order you get with select without order by is accidential). HTH Stefan Am Tuesday 02 September 2003 11:49 schrieb Alejandro Paz: Hi Stephan, Let's see the case : 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. 2. Inserting with the application : I get the records well 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. I thought that was clear when I said reverse order, sorry. So you see what I mean ? thanks ! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL
Re: insert ... select .. order by, problem
Roger, Thanks for the additional clarification Albert Atlanta - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Albert [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 8:00 AM Subject: Re: insert ... select .. order by, problem * Albert Stefan, I'm Roger, but I reply anyway. :) Do you imply that tables cannot be sorted desc or asc based on one of the columns e.g. a last name? or am I misunderstanding you. In relational database theory the order of rows within the table is undefined, i.e. it is up to the server, and the server can re-organize a table at any time. If you want an ordered result, you have to use ORDER BY in your SELECT statement. However, the MySQL server has some features that can be used to achieve exactly what you ask. It is possible to sort a table physically, in order to do faster reads later. The order is however destroyed if you do additional inserts. See the ORDER BY option of the ALTER TABLE statement: ORDER BY allows you to create the new table with the rows in a specific order. Note that the table will not remain in this order after inserts and deletes. In some cases, it may make sorting easier for MySQL if the table is in order by the column that you wish to order it by later. This option is mainly useful when you know that you are mostly going to query the rows in a certain order; by using this option after big changes to the table, you may be able to get higher performance. URL: http://www.mysql.com/doc/en/ALTER_TABLE.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
