This is a pretty common problem (for us, at least :P) - whether the columns named in the ORDER BY clause refer to columns in the source table(s) or columns in the result seems to be something that not all databases agree on. I would have thought that the SQL standard laid this out pretty clearly, but apparently not. :) It's been our experience that the only truly reliable way to avoid this problem is to be explicit.
-Tom > -----Original Message----- > From: Will Leshner [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 26, 2005 10:18 AM > To: Forum SQLite > Subject: [sqlite] qualified names in WHERE clause > > I guess I never really noticed this before (since I only use SQLite, > of course :) ). But consider a query like this: > > SELECT test2.* FROM test2,test11 WHERE test2.id=test11.id > ORDER BY name > > If the 'name' column happens to be a column in both test2 and > test11, > then SQLite will return an error. You need to qualify 'name' with > 'test2.' to make the query acceptable. Apparently MySQL and > PostgreSQL are able to recognize that 'name' refers to the 'name' in > the result set and are able to disambiguate it. >
