SitePoint
1-2-3 Website
The Matrix
'San Pedro
What I would like to do is to sort the Title column correctly ignoring the symbols, article, and have the number “1” be sorted as “one.” If properly sorted, it should result to this:
The Matrix (Matrix, The)
1-2-3 Website (One-Two-Three Website)
'San Pedro (San Pedro)
SitePoint (SitePoint)
Is this possible with MySQL? If it is not, how can I solve this sorting issue without changing the name?
swampBoogie, can I use the code above for version 4.0.22 of MySQL?
No, this is not possible in MySQL. It is not even possible in ANY database management system.
The only possible solution, one that I first came across decades ago, is to have a totally separate “sort by” column which contains the data you want to use for sorting purposes. This would contain the values you have enclosed in parentheses in your example.
order by
case
when substring(title from 1 for 4) = 'The ' then substring(title from 5)
when substring(title from 1 for 2) = 'A ' then substring(title from 3)
when substring(title from 1 for 1) = '1' then Concat('One',substring(title from 2))
end
i’ve got some code similar to swampboogie’s which will also concatenate the snipped part onto the end after a comma
if you need it 
that is sexy.
what is it? will it work for v4.0.22?
select title /* title is the original title */
, case when substring_index(title,' ',1)
in ('a','an','the')
then concat(
substring(title,instr(title,' ')+1)
,', '
,substring_index(title,' ',1)
)
else title
end as title2 /* title2 is what you sort on */
from striparticles
order by title2
What if I wanted to add a WHERE clause using the newly created title2?
Like if I only wanted every title starting with ‘a’
Or, if possible, what if I wanted a-m and n-z?
I was doing something like this:
WHERE left(title2,1)=‘a’
but it wasn’t working out.
Ryan
push the query down into a subquery, so that you can the use the WHERE clause in the outer query
select *
from (
[COLOR="Blue"]select title /* title is the original title */
, case when substring_index(title,' ',1)
in ('a','an','the')
then concat(
substring(title,instr(title,' ')+1)
,', '
,substring_index(title,' ',1)
)
else title
end as title2 /* title2 is what you sort on */
from yourtable[/COLOR]
) as reformatted
where title2 like 'a%'
order
by title2
by the way, this is one of the few times it is okay to use “select star” – because the columns are defined right there in the subquery
I have this now :
$selectt = mysql_query("select *
from (
select film_title
, case when substring_index(film_title,' ',1)
in ('a','an','the')
then concat(
substring(film_title,instr(film_title,' ')+1)
,', '
,substring_index(film_title,' ',1)
)
else film_title
end as title2
from films
) as reformatted
where title2 like 'a%'
order
by title2",$dbh)
or die(mysql_error());
and am getting a mysql error:
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ‘select film_title , case when substring_index(film_title,’
Think I’m missing something
Ryan
yup, you missed the sticky thread which asks that if you’re on a really old version of mysql, please let us know every time you post a question, so that we won’t take time to develop a solution that you can’t implement anyway
could you please upgrade
My bad, I forgot this post wasn’t on my original thread where I had told that I’m still rocking 4.1.
Ryan
I have been delayed on upgrading cause I have 7 sites on that same server. Who knows if they’d all survive the upgrade (horror stories coming to mind).
Any alternate solution?
Ryan
*it wouldn’t be such a big deal if the same sites didn’t represent ALL my income. 
you would not get that error in 4.1, you must be on 3.23 or 4.0
select title /* title is the original title */
, [COLOR="red"]case when substring_index(title,' ',1)
in ('a','an','the')
then concat(
substring(title,instr(title,' ')+1)
,', '
,substring_index(title,' ',1)
)
else title
end[/COLOR] as title2 /* title2 is what you sort on */
from striparticles
where [COLOR="red"]case when substring_index(title,' ',1)
in ('a','an','the')
then concat(
substring(title,instr(title,' ')+1)
,', '
,substring_index(title,' ',1)
)
else title
end [/COLOR] like 'a%'
order
by title2
I’m losing my mind. You’re right, I’m rocking 4.0
Ryan
*I’ll evaluate upgrading again today. But soooo scared.
Works great by the way.
Ryan
Okay, I’ve taken what was discussed here, made it a two-table query, and modified it to allow for a group by to choose the Most Viewed films first, and then each one’s most viewed video. Here is what I have. I think I may be using the wrong JOIN, and I am still using the mysql 4.0 version (though I’m now running 5.x), so if there is a way to make this more efficient I’d be greatly appreciative:
SELECT films.film_id
, films.film_title
, films.film_keyw
, trailermobile.trailer_keyw
, case when substring_index(films.film_title,' ',1)
in ('a','an','the')
then concat(
substring(films.film_title,instr(films.film_title,' ')+1)
,', '
,substring_index(films.film_title,' ',1)
)
else films.film_title
end as title2
FROM films
LEFT OUTER
JOIN ( SELECT trailers.trailer_id
, trailers.film_id
, trailers.trailer_keyw
FROM trailers
WHERE trailers.trailer_title like '%Trailer'
ORDER BY trailers.views DESC
) AS trailermobile
ON trailermobile.film_id = films.film_id
where case when substring_index(films.film_title,' ',1)
in ('a','an','the')
then concat(
substring(films.film_title,instr(films.film_title,' ')+1)
,', '
,substring_index(films.film_title,' ',1)
)
else films.film_title
end like '$char%'
GROUP BY films.film_id
order by films.views DESC LIMIT 3
This way works, but I feel it isn’t responding as quick as my query I had before doing two columns. All suggestions/feedback appreciated.
Cheers
Ryan
yes, there are a couple of inefficiencies
first, the subquery to get trailers has an ORDER BY which is useless (i’m not sure if mysql isn’t smart enough to completely disregard it)
second, the GROUP BY is useless
Okay,
I removed the Order By and the query stopped working properly. Instead of giving me the top viewed video for each film, each just gave me any video for each film that matched ‘%Trailer’.
I put the Order By back and it started working again.
I removed the Group By and the query stopped working properly also. Instead of giving me the top three films with the top video for each one, it gave me the same film three times, with its top three videos.
So, if my top films are Transformers, Taken and Twilight and they have videos like the following
(films table) Transformers - 240k views
(trailers table)
Teaser Trailer - 82K views
Feature Trailer - 130k views
Trailer - 42k views
Taken - 180k views
Teaser Trailer - 140k views
Trailer B - 98k views
Feature Trailer - 70k views
Twilight - 220k views
Teaser Trailer - 80k views
International Trailer - 60k views
Trailer - 190k views
The trailers have the film_id of the film they relate too. When the query runs, I want the Limit of 3 to return:
Transformers - Feature Trailer
Twilight - Trailer
Taken - Teaser Trailer
So the top three films by views, with each one showing their top viewed video.
Cheers
Ryan
*oh, just for example, if I get rid of Group By I get as my result:
Transformers - Feature Trailer
Transformers - Teaser Trailer
Transformers - Trailer