Обсуждение: way to custom sort column by fixed strings, then by field's content
I have a column that contains items like
'absolute root'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.
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.
This is 9.2.
Susan
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.klaver@gmail.com
Hello ,
The case Syntax is not correct , have a look here
http://www.postgresql.org/docs/current/static/functions-conditional.html
Furthermore; I think, this will not ensure the order of root 3, root 4 and root 5 as well as scene1,...
Regards
On Monday, February 3, 2014 7:55 PM, Susan Cassidy <susan.cassidy@decisionsciencescorp.com> wrote:
I have a column that contains items like
'absolute root'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.
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.
This is 9.2.
Susan
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
-------------------+-------------
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.klaver@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.klaver@gmail.com
On 02/03/2014 12:01 PM, Susan Cassidy wrote: CCing the list. > This didn't sort the way I want. It ended up as: > 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 > > > The first column is the one I want sorted. Probably best to show us a made up sample of what you want. > > Also note that I made it a left outer join\ Where? > > Susan > -- Adrian Klaver adrian.klaver@gmail.com
On 02/03/2014 01:22 PM, Adrian Klaver wrote:
On 02/03/2014 12:01 PM, Susan Cassidy wrote:With which query?
CCing the list.This didn't sort the way I want. It ended up as:
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
The first column is the one I want sorted.
Probably best to show us a made up sample of what you want.
Also note that I made it a left outer join\
Where?
Susan
On 02/03/2014 01:22 PM, Adrian Klaver wrote:
On 02/03/2014 12:01 PM, Susan Cassidy wrote:
CCing the list.This didn't sort the way I want. It ended up as:
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
The first column is the one I want sorted.
Probably best to show us a made up sample of what you want.
Also note that I made it a left outer join\
Where?
Susan
Is this what you're after?
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;
s1 | s2 | v
-------------------+---------+---
absolute root | | 1
18 cm long wrench | Scene 1 | 3
blue screwdriver | Scene 1 | 3
red toolbox | Scene 1 | 3
root 3 | Scene 1 | 3
root 4 | Scene 2 | 3
root 6 | Scene 3 | 3
small wrench | Scene 1 | 3
tire | Scene 2 | 3
(9 rows)
On 02/03/2014 01:01 PM, Susan Cassidy wrote:
Sorry this is what I meant to postdescription | 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 2On Mon, Feb 3, 2014 at 11:14 AM, Adrian Klaver <adrian.klaver@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.klaver@gmail.com
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)
Susan Cassidy-3 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? Read the documentation for "CASE": http://www.postgresql.org/docs/9.3/interactive/functions-conditional.html#FUNCTIONS-CASE There are two forms: SF) CASE expression WHEN value THEN result LF) CASE WHEN condition THEN result The first form is a short-hand version for the second form using the common equality condition. Converting from SF to LF results in a condition of the form "expression = value" and thus there must be an equality operator between the type of "expression" and the type of "value". In your example "expression" is a string - description - while "value" is a boolean (string .op. string). This is because you incorrectly repeated the writing of the "expression" in each "WHEN" clause. For your problem you want to explicitly use the long-form so you have much more flexibility in your "conditions" than simple value-equality (i.e., your ilike is not possible in short-form) ORDER BY CASE WHEN sc.desc... = 'absol...' THEN 1 WHEN sc.desc... ilike 'root%' THEN 2 ELSE 3 END This causes the primary sort just like what you want. However, you have not specified how multiple "root" items should sort nor how everything else besides "root" and "absolute root" should sort. To do so you add a second sort expression - in this case just the description column. ORDER BY CASE ... END, sc.description Now all roots will be listed in ascending string order after "absolute root" and everything else will come after "root%" also in ascending string order. Adrian was close but simply reversed the order of the two expressions in the ORDER BY. More importantly, though, he did provide the correct "CASE" syntax. The typo of the column ordering was easily overlooked, and corrected for by the reader IMO, given the data sample in which the whole case part ended up irrelevant. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/way-to-custom-sort-column-by-fixed-strings-then-by-field-s-content-tp5790371p5790398.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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;
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)
On Mon, Feb 3, 2014 at 12:22 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On 02/03/2014 12:01 PM, Susan Cassidy wrote:
CCing the list.This didn't sort the way I want. It ended up as:
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
The first column is the one I want sorted.
Probably best to show us a made up sample of what you want.
Also note that I made it a left outer join\
Where?
Susan
--
Adrian Klaver
adrian.klaver@gmail.com
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 adrian.klaver@gmail.com
Works great, thanks a bunch.
Susan
Susan
On Mon, Feb 3, 2014 at 12:39 PM, Rob Sargent <robjsargent@gmail.com> wrote:
On 02/03/2014 01:01 PM, Susan Cassidy wrote:Sorry this is what I meant to postdescription | 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 2On Mon, Feb 3, 2014 at 11:14 AM, Adrian Klaver <adrian.klaver@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.klaver@gmail.com
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)