Re: RE - Order By Problem
andy thomas wrote: 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]
Re: RE - Order By Problem
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 ca
RE: RE - Order By Problem
On Tue, 8 Jun 2004, Andy Eastham wrote: > Andy, > > Just: > > select substring_index(surname,' ',-1) as r from advisers order by r; Yes, that did the trick! Thanks, Andy > > -Original Message- > > From: andy thomas [mailto:[EMAIL PROTECTED] > > Sent: 08 June 2004 15:57 > > To: Andy Eastham > > Cc: Mysql List > > Subject: RE: RE - Order By Problem > > > > 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] > > > > > > > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > # include -- 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
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
Andy, Just: select substring_index(surname,' ',-1) as r from advisers order by r; works. Andy > -Original Message- > From: andy thomas [mailto:[EMAIL PROTECTED] > Sent: 08 June 2004 15:57 > To: Andy Eastham > Cc: Mysql List > Subject: RE: RE - Order By Problem > > 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] > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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
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] > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- 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
Look at using the Reverse() function, then take the substring up to the first space, then reverse the result. 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] -- 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
On Tue, 8 Jun 2004, Vadim P. wrote:
> If "surname" is a field, then use it without the single quotes ('),
> otherwise it is treated as a literal string and 0 is the correct result:
>
> select locate(' ',surname,1) from advisers
Thanks a lot, this is working. I now need to figure out how to use the IF
syntax, etc (not done this before ;-) so that the result from thsi query
can be used as an argument for the next.
cheers,
Andy
> andy thomas wrote:
>
> >Yes, this is the approach I was thinking of using but:
> >
> > select locate(' ','surname',1) from advisers
> >
> >just returns 0 for all records, whether or not they contain the ' ' space
> >substring.
> >
> >
> >
> >
>
>
--
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
If "surname" is a field, then use it without the single quotes ('),
otherwise it is treated as a literal string and 0 is the correct result:
select locate(' ',surname,1) from advisers
andy thomas wrote:
Yes, this is the approach I was thinking of using but:
select locate(' ','surname',1) from advisers
just returns 0 for all records, whether or not they contain the ' ' space
substring.
--
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
On Tue, 8 Jun 2004, Paul McNeil wrote:
> 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.
Yes, this is the approach I was thinking of using but:
select locate(' ','surname',1) from advisers
just returns 0 for all records, whether or not they contain the ' ' space
substring.
> 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.
Well, this would probably work if I could get the above statement to work.
Andy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
