Hi, the column name in the order by - clause "name" has to match one of the columns of the select statement, because it is a union. Your columns are "id", "url" ad "selected", none of which is "name".
Obviously, your table does contain a column named "name", but because of the union this can not be used. The order by is executed after the union of the two result sets, and the column "name" ist not part of the result set. Use the following: SELECT id, url, selected, name FROM db1.test UNION SELECT id, url, selected, name FROM db2.test ORDER BY name ASC, id DESC LIMIT 100" Martin Susan Shippey wrote: > Hi, > > I get the following error with SQLite 3.6.1 > > "1st ORDER BY term does not match any column in the result set" > > From the following query > "SELECT id, url, selected, FROM db1.test UNION > SELECT id, url, selected, FROM db2.test > ORDER BY name ASC, id DESC LIMIT 100" > > However the equivalent with a single DB i.e. without the UNION seems to work > fine. > > Is there a general restriction that ORDER BY terms must be in the result > set? And if so why doesn't it kick in without the UNION? > > And is there a performance cost to adding the ORDER term to the result set, > given that we are ordering by it anyway? > > Many thanks, > Tom > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
