andy thomas wrote:
<snip>
Well, this was fixed in the end by this query:
select substring_index(surname,' ',-1) as r from advisers order by r
which produced the desired result. But we have since had complaints from individuals wanting their surnames sorted differently! People from Germany with surnames such as 'von Neumann' like to have this sorted with the V's and not the N's while people from the Netherlands with 'van den Berg' want it to be with the B's and not the V's.
We are now redesigning the table to allow records to be displayed in a specific order chosen by the administrator, rather than trying to do this automatically by a SELECT statement.
Wow, that sounds like a headache. Are you adding a sort order column, then? You'll have to renumber everytime you add a row. That will work, but I'd like to point out that my suggested solution easily handles this in either of two ways, depending on your sorting philosopphy:
#1: Each user's name is always sorted where he/she expects it. In this case, Max von Neumann expects to be sorted with the Vs, so 'von Neumann' goes in the surname column while surname_prefix is set to NULL. Meanwhile, Marije van den Berg expects to show up in the Bs, so 'van den' goes in surname_prefix and 'Berg' goes in surname. In your queries, ORDER BY surname.
#2: Since Max von Neumann expects to be sorted with the Vs, he probably expects Marije van den Berg to show up in the Vs, as well. Meanwhile, as Marije van den Berg expects to be in the Bs, she probably expects to find Max von Neumann in the Ns. To accomodate both, always put the prefix in the surname_prefix column and the rest in the surname column. Then create two listings: Max von Neumann sees, based on his preference, a listing with "ORDER BY surname_prefix, surname". Marije van den Berg, on the other hand, sees a listing, based on her preference, with "ORDER BY surname. In other words, each list *viewer* chooses whether surname_prefix will be significant in the ordering or not.
I'd probably go with option 2, as it enables each user to see the list sorted the way he/she expects.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
