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 <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Marco Bambini >Sent: Saturday, 29 February, 2020 01:38 >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >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
