On 06/11/06, Richard Lynch <ceo@xxxxxxxxx> 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
