Re: MySQL: Alphabetizing title results in library format
| From: | Petr Tuma | Date: | Thu, 01 Nov 2001 04:07:17 +0000 |
| Subject: | Re: MySQL: Alphabetizing title results in library format | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-13897@lists.php.net to get a copy of this message | ||
Hi,
I would assume that MySQL doesn't support functions in the 'order by' clause.
Try this:
select *,(case when Title like 'The %' then substring (Title, 5, 255)
when Title like 'A %' then substring (Title, 3, 255)
when Title like 'An %' then substring (Title, 4, 255)
else Title end) as sort_col from Title order by sort_col
(I'm not sure about the "case" function syntax :(
On Wednesday 31 October 2001 23:42, you wrote:
> Back when I was working with MSSQL I need to alphabetize movie titles in
> the proper library format where 'A' 'An" and 'The" are ignored.
>
> For MSSQL I was told to use:
> select *
> from Titles
> order by case when Title like 'The %' then substring (Title, 5, 255)
> when Title like 'A %' then substring (Title, 3, 255)
> when Title like 'An %' then substring (Title, 4, 255)
> else Title
> end
>
> That worked like a charm, yet MySQL doesn't seem to accept that. Any
> solutions or advice?
--
Petr Tuma
IT Manager, M.Ward Manufacturing