Re: [sqlite] Deferred foreign key constraint bug?
Am 21.01.2011 21:36, schrieb Steve Campbell: > To summarize, to reproduce this problem, the statements must not be enclosed > in a BEGIN-COMMIT block. But DEFERRABLE is all about transactions, isn't it? As far as I understand this feature... I use it a lot, because then I have no headache importing data (order does not matter any more). Jan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Order of UNION query results
On Fri, Jan 21, 2011 at 6:53 PM, Josh Gibbswrote: > Could someone please clarify for me if the the resulting order of a UNION > query will come back with the left data first, then the right data in > the case > that no ordering has been defined for the query. > > My need is to have a parameter stored in a database, with an optional > overriding parameter which should take precedence, such as: > > select value from param_overrides where key='setting' UNION > select value from params where key='setting' > > I'd like the resulting recordset to always contain the override parameter > first if it exists so I can simply use that value. > > Thanks, Josh > If you add a prio field (priority) to your param database, 0=highest priority (override), 1=normal priority, you only need 1 table. Then you can say: select value from params where key='setting' order by prio limit 1 Jim -- HashBackup: easy onsite and offsite Unix backup http://www.hashbackup.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Order of UNION query results
On Fri, Jan 21, 2011 at 6:53 PM, Josh Gibbswrote: > Could someone please clarify for me if the the resulting order of a UNION > query will come back with the left data first, then the right data in > the case > that no ordering has been defined for the query. > > My need is to have a parameter stored in a database, with an optional > overriding parameter which should take precedence, such as: > > select value from param_overrides where key='setting' UNION > select value from params where key='setting' > SELECT coalesce( (SELECT value FROM param_overrides WHERE key='setting'), (SELECT value FROM param WHERE key='setting) ); This approach above has the advantage that it never evaluates the second query if the first query is successful. > > I'd like the resulting recordset to always contain the override parameter > first if it exists so I can simply use that value. > > Thanks, Josh > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Order of UNION query results
On 22-01-11 00:53, Josh Gibbs wrote: > Could someone please clarify for me if the the resulting order of a UNION > query will come back with the left data first, then the right data in > the case > that no ordering has been defined for the query. > > My need is to have a parameter stored in a database, with an optional > overriding parameter which should take precedence, such as: > > select value from param_overrides where key='setting' UNION > select value from params where key='setting' > > I'd like the resulting recordset to always contain the override parameter > first if it exists so I can simply use that value. > > Thanks, Josh > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users select value, 1 as myorder from param_overrides where key='setting' UNION select value, 2 as myorder from params where key='setting' order by 2; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Order of UNION query results
The result without an order by clause is always undefined. It might be ordered to your liking in the current version and be different in the next version. Never rely on an ordering without an order by clause. Use this instead: select coalesce(po.value, pp.value) from params pp where key='setting' left join param_overrides po on pp.key=po.key -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Josh Gibbs Sent: zaterdag 22 januari 2011 0:54 To: General Discussion of SQLite Database Subject: [sqlite] Order of UNION query results Could someone please clarify for me if the the resulting order of a UNION query will come back with the left data first, then the right data in the case that no ordering has been defined for the query. My need is to have a parameter stored in a database, with an optional overriding parameter which should take precedence, such as: select value from param_overrides where key='setting' UNION select value from params where key='setting' I'd like the resulting recordset to always contain the override parameter first if it exists so I can simply use that value. Thanks, Josh ___ 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
[sqlite] Order of UNION query results
Could someone please clarify for me if the the resulting order of a UNION query will come back with the left data first, then the right data in the case that no ordering has been defined for the query. My need is to have a parameter stored in a database, with an optional overriding parameter which should take precedence, such as: select value from param_overrides where key='setting' UNION select value from params where key='setting' I'd like the resulting recordset to always contain the override parameter first if it exists so I can simply use that value. Thanks, Josh ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users