description | description -------------------+------------- 18 cm long wrench | Scene 1 absolute root | blue screwdriver | Scene 1 red toolbox | Scene 1 root 3 | Scene 1 root 4 | Scene 2 root 6 | Scene 3 small wrench | Scene 1 tire | Scene 2
On Mon, Feb 3, 2014 at 11:14 AM, Adrian Klaver <adrian.kla...@gmail.com>wrote: > On 02/03/2014 10:53 AM, Susan Cassidy wrote: > >> I have a column that contains items like >> 'absolute root' >> 'root 3' >> 'root 4' >> 'root 5' >> 'scene 1' >> 'scene 2' >> 'scene 3' >> >> and I would like them to sort in that order. >> >> I tried: >> select sti.description, sc.description from scene_thing_instances sti >> join scenes sc on sti.scene_id = sc.scene_id >> order by CASE sc.description >> when (sc.description = 'absolute root'::text) then 1 >> when (sc.description ilike 'root%') then 2 >> else 3 >> END; >> >> I was starting with this, and was going to add perhaps another case >> statement. >> >> But it gives me: >> ERROR: operator does not exist: text = boolean >> LINE 3: when (sc.description = 'absolute root'::text) th... >> ^ >> HINT: No operator matches the given name and argument type(s). You >> might need to add explicit type casts. >> >> I don't understand this because description is a text column, not >> boolean, and certainly 'absolute root'::text is a text string. >> >> This is 9.2. >> >> Ideas, anyone? >> > > select sti.description, sc.description from scene_thing_instances sti join > scenes sc on sti.scene_id = sc.scene_id > order by sc.description, CASE > when (sc.description = 'absolute root'::text) then 1 > when (sc.description ilike 'root%') then 2 > else 3 > END; > > >> Thanks, >> Susan >> > > > -- > Adrian Klaver > adrian.kla...@gmail.com >
