Right, ORDER by, oherwise the row order is undefined. Why the urge to grab the mailing list, then look at the references? Dunno, sloppy hobbyist..
On Sat, Apr 2, 2011 at 5:14 PM, Jay A. Kreibich <[email protected]> wrote: > On Sat, Apr 02, 2011 at 05:01:34PM -0700, Matt Young scratched on the wall: > > sqlite> select 1 as type union select 2; > > type > > 1 > > 2 > > sqlite> select 'tr' as type union select 2; > > type > > 2 > > tr > > sqlite> > > Yes. > > First, understand that the row order of all SQL queries is undefined, > unless there is an explicit ORDER BY clause. As such, this is > perfectly valid because there is no defined ordering. > > As for why it is happening, the "union" operator will only return > distinct rows. The easiest way to find duplicate rows it to perform > a sort. Both of these results are actually sorted, you just don't > see it in the first example. > > To eliminate the sort, you can use "union all", which does not > remove duplicates. > > Consider: > > sqlite> select 2 as type union select 1; # sorted result > type > 1 > 2 > > sqlite> select 'tr' as type union all select 2; # unsorted result > type > tr > 2 > > > -j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Intelligence is like underwear: it is important that you have it, > but showing it to the wrong people has the tendency to make them > feel uncomfortable." -- Angela Johnson > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
