On Tue, 8 Jun 2004, Michael Stassen wrote:
> The proposed solution to sort on a portion of the surname field will work,
> but it has a drawback. If you sort on the result of a function applied to a
> column, you prevent the use of any index on that column. If your data set
> and user base are both small, this may be a problem you can ignore, but it
> won't scale well. Also, I expect you will want 'de la Tour' to come before
> 'du Tour', so you'll have to do a secondary sort on surname.
The table is quite small with only 33 records at present although it gets
accessed maybe 10K times a day.
> I'd like to suggest an alternate solution. In your current scheme, you
> would put 'de la Tour' in your surname column, but you are saying that
> 'Tour' is the part to sort by, while 'de la' is not. To my mind, that means
> 'de la' and 'Tour' are different kinds of data, which means they belong in
> different columns -- surname_prefix and surname, perhaps. Then you can
> concatenate surname_prefix and surname for display purposes, but sort on
> just surname (or surname, surname_prefix, first_name), and an index on
> surname (or surname, surname_prefix, first_name) could be used.
>
> For example:
>
> SELECT * FROM advisers;
>
> +----+------------+----------------+----------+
> | id | first_name | surname_prefix | surname |
> +----+------------+----------------+----------+
> | 1 | Michael | NULL | Stassen |
> | 2 | Max | van den | Berg |
> | 3 | Sylvia | du | Sautoy |
> | 4 | Alicia | NULL | Davidson |
> | 5 | Marco | van | Basten |
> | 6 | Andy | NULL | Thomas |
> | 7 | Michelle | de | Contes |
> | 8 | Gabrielle | de la | Tour |
> | 9 | Joe | NULL | McNeil |
> | 10 | Chris | NULL | Brown |
> +----+------------+----------------+----------+
> 10 rows in set (0.30 sec)
>
>
> SELECT first_name, CONCAT_WS(' ', surname_prefix, surname) AS last_name
> FROM advisers
> ORDER BY surname;
>
> +------------+--------------+
> | first_name | last_name |
> +------------+--------------+
> | Marco | van Basten |
> | Max | van den Berg |
> | Chris | Brown |
> | Michelle | de Contes |
> | Alicia | Davidson |
> | Joe | McNeil |
> | Sylvia | du Sautoy |
> | Michael | Stassen |
> | Andy | Thomas |
> | Gabrielle | de la Tour |
> +------------+--------------+
>
> SELECT CONCAT_WS(' ', first_name, surname_prefix, surname) AS name
> FROM advisers
> ORDER BY surname, surname_prefix, first_name;
>
> +----------------------+
> | name |
> +----------------------+
> | Marco van Basten |
> | Max van den Berg |
> | Chris Brown |
> | Michelle de Contes |
> | Alicia Davidson |
> | Joe McNeil |
> | Sylvia du Sautoy |
> | Michael Stassen |
> | Andy Thomas |
> | Gabrielle de la Tour |
> +----------------------+
Yes, this is one way of doing this. But having adopted an alternative
solution based on a suggestion from Andy Eastham, it now turns out that
the users of the database from different countries have different ideas
of how we should be ordering surnames! So to keep everyone happy, the
table is being redesigned to allow entries to be ordered as the users
want them ordered, rather than the way *we* think they should be ordered.
Complicated but that's life...
cheers,
Andy
> andy thomas wrote:
>
> > On Tue, 8 Jun 2004, Andy Eastham wrote:
> >
> >
> >>Look at using the Reverse() function, then take the substring up to the
> >>first space, then reverse the result.
> >
> >
> > Well, 'select substring_index(surname,' ',-1) from advisers' does the
> > trick as far as extracting the wanted parts of surnames at the end of
> > the surname filed but I'm not sure how to use this as an argument to
> > ORDER BY? Shouldn't something like:
> >
> > select substring_index(surname,' ',-1) as r from advisers, select * from
> > advisers order by r
> >
> > work?
> >
> > Thanks for your help,
> >
> > Andy
> >
> >
> >>>-----Original Message-----
> >>>From: Paul McNeil [mailto:[EMAIL PROTECTED]
> >>>Sent: 08 June 2004 14:04
> >>>To: [EMAIL PROTECTED]
> >>>Subject: RE - Order By Problem
> >>>
> >>>I have never done anything like this but after looking at the spec's I
> >>>have a possible direction for you....
> >>>
> >>>In String functions there is LOCATE(substr,str,pos)
> >>>The first syntax returns the position of the first occurrence of substring
> >>>substr in string str. The second syntax returns the position of the first
> >>>occurrence of substring substr in string str, starting at position pos.
> >>>Returns 0 if substr is not in str.
> >>>
> >>>I think that if you create a function that uses this to strip the string
> >>>to the left of the last found space and that returns the string to the right
> >>>you could call this in your query and use it in the order by statement.
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
