Looks like I'm on my own with this one :) After a bit of fiddling with it this morning, I figured I had to do this
SELECT DISTINCT * FROM (SELECT t1.ID FROM GR_ADDRESS t1 WHERE t1.ID > 1 UNION ALL SELECT t1.ID FROM PERSON t1 ORDER BY t1.ID DESC) instead of SELECT DISTINCT * FROM (SELECT t1.ID FROM GR_ADDRESS t1 WHERE t1.ID > 1 UNION ALL SELECT t1.ID FROM PERSON t1) t1 ORDER BY t1.ID DESC Although it bugs me that the latter seems to be perfectly valid chunk of SQL as far as SQLite documentation is concerned and as far as other DBs are used to doing things. Is this intentional or just one little quirk? Cheers! -Boris -- +1.604.689.0322 DeepCove Labs Ltd. 4th floor 595 Howe Street Vancouver, Canada V6C 2T5 [EMAIL PROTECTED] CONFIDENTIALITY NOTICE This email is intended only for the persons named in the message header. Unless otherwise indicated, it contains information that is private and confidential. If you have received it in error, please notify the sender and delete the entire message including any attachments. Thank you. -----Original Message----- From: Boris Popov [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 01, 2006 11:59 PM To: [email protected] Subject: RE: [sqlite] ORDER BY of UNION? Okay that works, but is there a way to make it work with the t1 alias? I'm porting an existing application to SQLite and changing all queries to not use aliases may be problematic if you know what I mean. Looking at the syntax page I don't see how using t1 is illegal and yet clearly as doesn't work as expected. Any more hints, ideas or suggestions? Thanks! -Boris -- +1.604.689.0322 DeepCove Labs Ltd. 4th floor 595 Howe Street Vancouver, Canada V6C 2T5 [EMAIL PROTECTED] CONFIDENTIALITY NOTICE This email is intended only for the persons named in the message header. Unless otherwise indicated, it contains information that is private and confidential. If you have received it in error, please notify the sender and delete the entire message including any attachments. Thank you. -----Original Message----- From: Darren Duncan [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 01, 2006 10:19 PM To: [email protected] Subject: Re: [sqlite] ORDER BY of UNION? At 8:59 PM -0800 3/1/06, Boris Popov wrote: >I can't seem to get unions to sort properly, > >SELECT DISTINCT * FROM (SELECT t1.ID >FROM GR_ADDRESS t1 UNION ALL SELECT t1.ID >FROM PERSON t1) t1 ORDER BY t1.ID DESC > >results in "no such column: t1.ID" error. How would I go about sorting the >result set in this case? > >Cheers! >-Boris Try removing the "t1." from both inner select statements, so it just says "select id" in both places. -- Darren Duncan
smime.p7s
Description: S/MIME cryptographic signature
