From: | Lincoln Yeoh <lylyeoh(at)mecomb(dot)com> |
---|---|
To: | pgsql-general(at)hub(dot)org |
Subject: | Does Order by support case? |
Date: | 2000-05-19 10:12:36 |
Message-ID: | 3.0.5.32.20000519181236.008ff900@pop.mecomb.po.my |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Matthias Urlichs | 2000-05-19 10:18:03 | Re: Performance (was: The New Slashdot Setup (includes MySql server)) |
Previous Message | Matthias Urlichs | 2000-05-19 09:14:24 | Re: Performance (was: The New Slashdot Setup (includes MySql server)) |