Re: [sqlite] Select statement with ORDER BY specified by column value

[Keith Medcalf]

SELECT prop_value FROM Table1 WHERE obj_id=10 AND prop_key='key1' AND 
(prop_tag='ios' OR prop_tag='*') ORDER BY prop_tag == 'ios' DESC LIMIT 1;

You want to order by prop_tag == 'ios' in DESCENDING order.  That is, the true 
(1) before the false (0).  The default ascending sort will sort the false (0).  
 
before the true (1) ...  This will work because prop_tag cannot be null.  If it 
could, then nulls would sort first always (they are the firstest before 
consideration of order by ascending or descending) unless you did something 
about it.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Marco Bambini
>Sent: Saturday, 29 February, 2020 01:38
>To: SQLite mailing list 
>Subject: [sqlite] Select statement with ORDER BY specified by column
>value
>
>Hi all,
>
>Is there a way to specify an ORDER BY clause by column value?
>
>I have a table declared as:
>CREATE TABLE Table1 (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id
>INTEGER, prop_key TEXT, prop_value TEXT, prop_tag TEXT DEFAULT '*',
>UNIQUE(obj_id, prop_key, prop_tag))
>
>and a sample query:
>SELECT prop_value FROM Table1 WHERE obj_id=10 AND prop_key='key1' AND
>(prop_tag='ios' OR prop_tag='*') ORDER BY (prop_tag='ios') LIMIT 1;
>
>I would like to prioritise results based on the fact that the prop_tag
>column is 'ios'.
>
>Thanks.
>--
>Marco Bambini
>https://www.sqlabs.com
>
>
>
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select statement with ORDER BY specified by column value

[Simon Slavin]

On 29 Feb 2020, at 8:37am, Marco Bambini  wrote:

> ORDER BY (prop_tag='ios') LIMIT 1;
> 
> I would like to prioritise results based on the fact that the prop_tag column 
> is 'ios'.

SQLite has a conditional construction:

CASE prop_tag WHEN 'ios' THEN 0 ELSE 1 END

So do

SELECT …
ORDER BY CASE prop_tag WHEN 'ios' THEN 0 ELSE 1 END
LIMIT 1;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Select statement with ORDER BY specified by column value

[Marco Bambini]

Hi all,

Is there a way to specify an ORDER BY clause by column value?

I have a table declared as:
CREATE TABLE Table1 (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id INTEGER, 
prop_key TEXT, prop_value TEXT, prop_tag TEXT DEFAULT '*', UNIQUE(obj_id, 
prop_key, prop_tag))

and a sample query:
SELECT prop_value FROM Table1 WHERE obj_id=10 AND prop_key='key1' AND 
(prop_tag='ios' OR prop_tag='*') ORDER BY (prop_tag='ios') LIMIT 1;

I would like to prioritise results based on the fact that the prop_tag column 
is 'ios'.

Thanks.
--
Marco Bambini
https://www.sqlabs.com




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users