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 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
