On 02/03/2014 01:00 PM, Susan Cassidy wrote:
The query is currently:
select sti.description, sc.description from scene_thing_instances sti
left outer join scenes sc on sti.scene_id = sc.scene_id
order by sti.description, CASE
when (sti.description = 'absolute root'::text) then 1
when (sti.description ilike 'root%') then 2
else 3
END;
The results I want are:
description | description
-------------------+-------------
absolute root |
root 3 | Scene 1
root 4 | Scene 2
root 6 | Scene 3
18 cm long wrench | Scene 1
blue screwdriver | Scene 1
red toolbox | Scene 1
small wrench | Scene 1
tire | Scene 2
(9 rows)
So Robs last solution:
select s.s1, s.s2, ( CASE
when (s.s1 = 'absolute root'::text) then 1
when (s.s1 ~* '^root*') then 2
else 3
END) as v
from scripts as s
order by v,s1
toys-# ;
s1 | s2 | v
-------------------+---------+---
absolute root | | 1
root 3 | Scene 1 | 2
root 4 | Scene 2 | 2
root 6 | Scene 3 | 2
18 cm long wrench | Scene 1 | 3
blue screwdriver | Scene 1 | 3
red toolbox | Scene 1 | 3
small wrench | Scene 1 | 3
tire | Scene 2 | 3
(9 rows)
--
Adrian Klaver
[email protected]
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
