In addition to what was already said, if you absolutely to omit the 'name' from the final result set, you should be able to put the result of the union in a temporary table.
CREATE TEMPORARY TABLE `tmp` (`id`, `url`, `selected`, `name`); INSERT INTO `tmp` ( 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); SELECT `id`, `url`, `selected` FROM `tmp`; Takes a little more CPU, but gets the job done. Although my expertise is more on the MySQL side, so Sqlite might have some kind of limitation I don't know about. On Tue, Jun 9, 2009 at 1:20 PM, <cmar...@unc.edu> wrote: > On Tue, 9 Jun 2009, Simon Slavin wrote: > > >> 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" > > > > I would add to Marin's answer that reading the above, I have no idea > > which database the column 'name' is in. Suppose db1 and db2 both had > > a 'name' column. Which one would it be. So it might be better to use > > > > SELECT id, url, selected, name FROM db1.test UNION > > SELECT id, url, selected, name FROM db2.test > > ORDER BY db1.name ASC, db2.id DESC LIMIT 100 > > The answer to the question 'If both had a name column, which one would it > be?' is both. The UNION operator creates a resultset having a single name > column which contains values from both tables. ORDER BY operates on this > combined resultset, it cannot distinguish between the name values > contributed by db1 and those contributed by db2. The ordering is only > performed on the combined results, not on the separate SELECT clauses. > Once the UNION is generated, there is only a single name column to be > sorted. So the 'ORDER BY db1.name' shown above gives the same results as > if you entered 'ORDER BY name'. The 'db1' prefix is ignored in this > context. > > The question "suppose both had a name column" may reflect a > misunderstanding of the UNION. Each SELECT clause of a union must > contribute the same number of columns, thus, it is not possible to have > 'name' coming only from one SELECT clause and not the other. For instance, > this will fail: > > SELECT id, url, selected, name FROM db1.test UNION > SELECT id, url, selected FROM db2.test; > > It fails because the first SELECT has 4 cols, the 2nd has 3. If db2.test > in fact does not have a name column, but you want include name values from > db1, you need to use a placeholder like this: > > SELECT id, url, selected, name FROM db1.test UNION > SELECT id, url, selected, NULL FROM db2.test; > > or this > > SELECT id, url, selected, name FROM db1.test UNION > SELECT id, url, selected, ' ' FROM db2.test; > > The result of these queries will have values from db1 for name, but the > name column for data contributed by db2 will be NULL/empty. > > Chris > _______________________________________________ > 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
