----- Original Message ----- From: "Paul Hanlon" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, October 07, 2004 4:55 AM Subject: Referring to columns by ordinal
> Hi all, > I've searched all over the place for a solution to this, and I hope you can > help. I'm trying to write a very simple database handling script. The SQL > statement I'm trying to do is SELECT * FROM tablename WHERE the_first_column > = a_number; > All my tables have the primary key in the first column, but they are called > different names. What can I use to replace the_first_column. > Sorry, I don't think there is any way to use ordinals in a WHERE clause. You will have to use the actual name of the first column. According to the manual, http://dev.mysql.com/doc/mysql/en/Problems_with_alias.html, you can use an alias to refer to a column in GROUP BY, ORDER BY, or HAVING but *not* in WHERE. The reason: "This is because when the WHERE code is executed, the column value may not yet be determined." Now, this refers to an alias which you have defined in a SELECT clause, like 'Select count(*) as num", not an ordinal. I don't see anything that explicitly says that you can't have an ordinal in a WHERE so there is always the possibility that an ordinal is valid in a WHERE. However, I think you can disprove that possibility very quickly by trying a query like: select * from mytable where 1 = 'Jones'; This assumes that the first column of your table contains surnames. I think you'll see that this doesn't work. There's at least one very practical reason why it would be very confusing if ordinals were allowed in WHERE clauses. Suppose you had a table that contained integers in the some columns and you used an ordinal to represent the column position instead of using its name. Consider this query: select * from mytable where 2 = 7; Is this query trying to find all the rows where the value in the second column is 7 or all the rows where the value in the 7th column is 2? The only way to prevent a misinterpretation here is to insist that the integer to the left of the equal sign is always a column ordinal and the integer to the right of the equal sign is always a literal. The only solution I can see for your problem would be to re-create all of your tables and this time name the first column of each table something like 'key' or 'primary key'. Then, all your queries could say something like: select * from mytable where key = 7; Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
