Re: [sqlite] Order of UNION query results

[Josh Gibbs]

Excellent and perfect solution to my problem.  Thanks Richard.


On 23/01/2011 2:16 a.m., Richard Hipp wrote:
> On Fri, Jan 21, 2011 at 6:53 PM, 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'
>>
> 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
>>
>
>


___
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

[Jim Wilcoxson]

On Fri, Jan 21, 2011 at 6:53 PM, 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
>

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

[Richard Hipp]

On Fri, Jan 21, 2011 at 6:53 PM, 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'
>

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

[luuk34]

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

[Jos Groot Lipman]

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

[Josh Gibbs]

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