Re: Sorting MySQL queries

On 06/11/06, Richard Lynch <ceo@l-i-e.com> wrote:

On Sun, November 5, 2006 7:13 am, Dotan Cohen wrote: I have a list of subjects, such as "Linux", "Open Source", and "the World Wide Web". The subjects are stored in a database and being retrieved via php. I currently organize them alphabetically with SQL's ORDER BY ASC argument, however, if there is a preceding "the " or "a " then that is considered as part of the alphabetical order. Thus, all the subjects starting with "the " are grouped together, as are the subjects starting with "a ". How can I order by ascending, without taking the preceding "the " or "a " into account? ** Example: Now, the list is ordeded like this: a Distribution a Text Editor a Virus Bluetooth Copyleft DRM Fedora Firefox However, I'd like it to be ordered like this: Bluetooth Copyleft a Distribution DRM Fedora Firefox a Text Editor a Virus Current code: $query = "SELECT subject FROM table ORDER BY subject asc"; $result = mysql_query($query); I forget the SQL 'CASE' syntax, but it goes something like this: $query = "SELECT subject, case when substring(lower(subject, 0, 4) = 'the ') then substring(subject, 4) when substring(lower(subject, 0, 3) = 'an ' then substring(subject, 3) when substring(lower(subject, 0, 2) = 'a ') then substring(subject, 2) else subject) as subject_alpha FROM table ORDER BY subject_alpha"; 'Course, that renders ineffective any index you got on 'subject', almost for sure. If the table is HUGE and/or performance is an issue, you could set up a second field called subject_alpha which holds the alphabetized version. Then you need to be sure it's always kept in sync by your application. Or triggers and all that in later MySQL. Actually, super-recent MySQL has user-defined functions, I think, so the previous solution by another post may be better in that case. I do not think there is a magic built-in easy MySQL function for this, though you never know unless you read MySQL docs and ask on the MySQL mailing list. (Hint, hint.)

I just subscribed back to the MySQL list (unsubscribed a few months ago) to ask the question. If there's no super-smart way to do it, then I'll use the pseudo-subject field and sort on it. Performance-wise, I agree that it can't be beat. Thanks. Dotan Cohen http://lyricslist.com/ http://what-is-what.com/what_is/xml.html