Re: RE - Order By Problem

Michael Stassen Tue, 08 Jun 2004 10:57:58 -0700

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.

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 |
+----------------------+

Michael

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]

Re: RE - Order By Problem

Reply via email to