Ordering a MySQL Query by Most Common Words in a field

Question

I've always wondered about this, and now a client is wondering whether it's feasable:

I have a list of sports, broken down like such:

• Boys Ice Hockey
• Boys Tennis
• Girls Ice Hockey
• Girls Tennis

...etc

And they want it to sort like:

• Boys Ice Hockey
• Girls Ice Hockey
• Boys Tennis
• Girls Tennis

...etc

I'm just wondering if this is possible by simply altering the query. Unfortunately, I cannot set the sports to be be gender non-specific and have their gender set as a seperate field. I cannot edit the database in any significant way. I figured the best way to approach the problem is a "most words in common" query, but I have no idea how this would be done.

Thanks in advance for any help you guys can offer!

UPDATE: The client ended up dropping the request, however, I would have approached it most likely as the accepted answer. Still not a completely satisfying way of doing things, but I'm not sure there is a more "elegant" solution available in MySQL.

Answer 1

'Most Words in Common' as in compare the common words in each row with the next row? That will be pretty hard in SQL because its hard to get a query to compare different rows with each other.

You could order by a prescriptive CASE statement, but it wont be pretty:

...
order by CASE sport_name
    WHEN 'Boys Ice Hockey' THEN 1
    WHEN 'Girls Ice Hockey' THEN 2
    WHEN 'Boys Tennis' THEN 3
    WHEN 'Girls Tennis' THEN 4
    -- etc
END CASE

Answer 2

Can you do something like

ORDER BY SUBSTRING(sport_name, 6), SUBSTRING(sport_name, 1, 5)

in mysql if all the sports begin with Boys/Girls?

Alternatively the below might be better if space is being used as a delimiter:

ORDER BY SUBSTRING(sport_name, LOCATE(sport_name, ' ')), SUBSTRING_INDEX(sport_name, ' ', 1)

Completely untested as I havent tried this before and dont have a mysql instance to test it against