Hi, I'm hoping someone here can help me out with a query. I have multiple tables, each with the same schema. For example:
========= Table A: ========= 1|"xxx" 2|"yyy" 3|"zzz" ========= ========= Table B: ========= 1|"xxx222" 3|"zzz222" 5|"www" ========= I'd like a SELECT statement that yields: ========= Result: ========= 1|"xxx" 2|"yyy" 3|"zzz" 5|"www" ========= In other words, I want the UNION of all the input tables, but if there are multiple results that have the same value in the first column, the first table's value should take precedence. This seems like a common scenario, so I'm probably missing something trivial. :) But so far, the only way I've figured out to do this is with something like: SELECT * FROM (SELECT 1 AS precedence, col1, col2 FROM A UNION SELECT 2 AS precedence, col1, col2 FROM B ORDER BY col1 ASC, precedence DESC) GROUP BY precedence ORDER BY col1 ASC; (Just an example, I've got several other columns that have to be sorted on, and there can be any number of tables). This seems to do what I want, but it takes an order of magnitude longer than the inner SELECTs do on their own (i.e. without the GROUP BY which eliminates rows with duplicate 'col1' values). Any ideas on how I could do this more efficiently? Thanks! -- Matthew L. Creech _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
