Works great, thanks a bunch. Susan
On Mon, Feb 3, 2014 at 12:39 PM, Rob Sargent <robjsarg...@gmail.com> wrote: > On 02/03/2014 01:01 PM, Susan Cassidy wrote: > > 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 >> > > Sorry this is what I meant to post > > 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) > >
