Select statement with ORDER BY specified by column value

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Select statement with ORDER BY specified by column value

Simon Slavin-3
On 29 Feb 2020, at 8:37am, Marco Bambini <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Select statement with ORDER BY specified by column value

Keith Medcalf
In reply to this post by Marco Bambini

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 <[hidden email]> On
>Behalf Of Marco Bambini
>Sent: Saturday, 29 February, 2020 01:38
>To: SQLite mailing list <[hidden email]>
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users