Does Order by support case?

Поиск
Список
Период
Сортировка
От Lincoln Yeoh
Тема Does Order by support case?
Дата
Msg-id 3.0.5.32.20000519181236.008ff900@pop.mecomb.po.my
обсуждение исходный текст
Ответ на Re: Zip Code Proximity  (Dustin Sallings <dustin@spy.net>)
Ответы Re: Does Order by support case?
Список 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.


В списке pgsql-general по дате отправления:

Предыдущее
От: Dustin Sallings
Дата:
Сообщение: Re: Performance
Следующее
От: "Nikolay Mijaylov"
Дата:
Сообщение: pgsql for win