Hi everyone,
I'm trying to sort email style subject lines and similar stuff.
When I try: (on 6.5.3)
select m_subject from wm_mails
where m_id > 900 and m_id < 1000
order by
(CASE WHEN (lower(m_subject) like 're: %')
THEN (substring(lower(m_subject) from 5))
ELSE
(lower(m_subject))
END
);
I get
Illegal ORDER BY node = 723
In this case I'm trying to ignore leading 're: ' strings in the sort.
I found that I can use something like
select m_subject, case when ... then ... else end as orderfield
etc order by orderfield
I also managed to do it by creating a custom function:
create function trimsub (text)
returns text
as
'select CASE WHEN (lower($1) like \'re: %\')
THEN (substring(lower($1) from 5))
ELSE (lower($1))
END
'
language 'SQL'
;
But are there any other options where I can just put stuff in the "order
by" clause?
Thanks,
Link.
