RE: ORDER BY problem
Try your query with either back quotes around Company SELECT * FROM Contacts WHERE Categories=Services and BusinessCodes REGEXP ^R and gold_id=2 ORDER BY `Company` ASC Or no quotes around Company SELECT * FROM Contacts WHERE Categories=Services and BusinessCodes REGEXP ^R and gold_id=2 ORDER BY Company ASC -Original Message- From: Obantec Support [mailto:[EMAIL PROTECTED] Sent: Friday, July 11, 2008 9:29 AM To: mysql@lists.mysql.com Subject: ORDER BY problem Hi while testing an upgrade from 3.23.58 to 4.1.22 on an FC3 test box SELECT * FROM Contacts WHERE Categories=Services and BusinessCodes REGEXP ^R and gold_id=2 ORDER BY Company ASC on mysql server3.23.58 i get company result in ASC order. on mysql server4.1.22 i get non ASC order for companies. is there something on 4.1.22 i missed that effects ORDER BY? Mark -- 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: ORDER BY problem
- Original Message - Subject: RE: ORDER BY problem Try your query with either back quotes around Company SELECT * FROM Contacts WHERE Categories=Services and BusinessCodes REGEXP ^R and gold_id=2 ORDER BY `Company` ASC Or no quotes around Company SELECT * FROM Contacts WHERE Categories=Services and BusinessCodes REGEXP ^R and gold_id=2 ORDER BY Company ASC -Original Message- From: Obantec Support [mailto:[EMAIL PROTECTED] Sent: Friday, July 11, 2008 9:29 AM To: mysql@lists.mysql.com Subject: ORDER BY problem Hi while testing an upgrade from 3.23.58 to 4.1.22 on an FC3 test box SELECT * FROM Contacts WHERE Categories=Services and BusinessCodes REGEXP ^R and gold_id=2 ORDER BY Company ASC on mysql server3.23.58 i get company result in ASC order. on mysql server4.1.22 i get non ASC order for companies. is there something on 4.1.22 i missed that effects ORDER BY? Mark Hi Rolando please only reply to the list. Ok fixed now by either method. I have chosen to leave the off. Thanks Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: order desc problem
Hi, the order comes out of sequence showing 10.11.12.13 etc before the number 2--- Can anyone help me out That's because you are sorting the result on a string (char/varchar) column. Try using CAST to convert it to int or something similar: ORDER BY cast(column as unsigned) Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: order desc problem
At 20:27 +0800 9/7/06, M B Neretlis wrote:
the order comes out of sequence showing 10.11.12.13 etc before the number 2---
Can anyone help me out
?php
//get user tips
$query = @mysql_query(SELECT * FROM tips WHERE user_id = $user_id
AND comp_id = $comp_id ORDER by round DESC);
while ($result = @mysql_fetch_array($query)) {
?
Coo - something I actually know!
What column type is round? I bet it's a varchar or some other
non-numeric type. If I'm right, it's sorting lexically, so 1 comes
before 11, comes before 2, etc.
Change it to a some flavour of int and it should work.
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
But what ... is it good for?
-- Engineer at the Advanced Computing Systems Division of IBM,
commenting on the microchip, 1968
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY problem with JOINs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 10 September 2004 18:00, Ren Fournier wrote: I've got a SELECT statement that is returning the data I want, but not in the right order (and I don't know why...). Let's say there are two tables, People and History. Some records in People have corresponding records in History, but not all--so I need a LEFT JOIN TO connect history.people_id to people.id. So far, so good. But I want to order the list according to the timestamp column in history (history.time_sec), and this does not happen: Records are returned, but not in the right order. Here's my query: SELECT * FROM people LEFT JOIN history ON people.id = history.people_id GROUP BY people.id ORDER BY history.time_sec DESC It seems I can sort correctly on a field in people, but not on a field in historyis that because it is a left-joined table? I think it's because you're trying to sort on missing data. How can it sort on a field that isn't always there? Remember NULL doesn't compare as less than, equal *OR* greater than another value. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFBQjZljeziQOokQnARAidWAJ9zr+/x6EWJ8xTYCsmbvQVy5gMOIACgku3v KGWramLsfIBe7zwm8csGvwM= =hRZV -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY problem with JOINs
At 17:00 -0600 9/10/04, René Fournier wrote: I've got a SELECT statement that is returning the data I want, but not in the right order (and I don't know why...). Let's say there are two tables, People and History. Some records in People have corresponding records in History, but not all--so I need a LEFT JOIN TO connect history.people_id to people.id. So far, so good. But I want to order the list according to the timestamp column in history (history.time_sec), and this does not happen: Records are returned, but not in the right order. Here's my query: SELECT * FROM people LEFT JOIN history ON people.id = history.people_id GROUP BY people.id ORDER BY history.time_sec DESC It seems I can sort correctly on a field in people, but not on a field in history-is that because it is a left-joined table? We might be able to give you an answer if you show some results and indicate why you believe they are incorrect. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- 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: 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 std-disclaimer.h -- 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 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
Re: RE - Order By Problem
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]
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
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]
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, 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
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, 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
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
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: Order by problem
Hi, == I have an ID field in my database...it reads like this ASS1 ASS23 ASS4 ASS10 ASS6 when i'm retrieving the data by taking ORDER BY clause it is sorting like this ASS1 ASS10 ASS23 ASS4 ASS6 means its only sorting by the 4 the character. i want the sorting to be done like the following == No, it means it's sorting by alphabet, not by the 4th character. ASS10 comes after ASS1, makes perfect sense. == ASS1 ASS4 ASS6 ASS10 ASS23 Solutions are greatly appreciated == What you want, is that the sorting acts like to ignore ASS and use the number behind it as an integer. Perhaps you can cut off the first 3 characters, cast the rest to an Integer and order by that? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by problem
Martijn Tonies wrote: Hi, == I have an ID field in my database...it reads like this ASS1 ASS23 ASS4 ASS10 ASS6 when i'm retrieving the data by taking ORDER BY clause it is sorting like this ASS1 ASS10 ASS23 ASS4 ASS6 means its only sorting by the 4 the character. i want the sorting to be done like the following == No, it means it's sorting by alphabet, not by the 4th character. ASS10 comes after ASS1, makes perfect sense. == ASS1 ASS4 ASS6 ASS10 ASS23 Solutions are greatly appreciated == What you want, is that the sorting acts like to ignore ASS and use the number behind it as an integer. Perhaps you can cut off the first 3 characters, cast the rest to an Integer and order by that? SELECT * FROM tablename ORDER BY ((SUBSTRING(columnToSort FROM 4)) + 0) as numberpart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by problem
Sagar C Nannapaneni wrote: ASS1 ASS23 ASS4 ASS10 ASS6 . . when i'm retrieving the data by taking ORDER BY clause it is sorting like this ASS1 ASS10 ASS23 ASS4 ASS6 means its only sorting by the 4 the character. No, it's not sorted by the first four characters but it's sorted lexicographically (string-like). The following might help, but will certainly be quite slow: SELECT ... ORDER BY ABS(SUBSTRING(field, 4)); A better (and faster) solution will probably be indexing the records with a numeric field, as usual. Greetz Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by problem
At 06:49 AM 1/23/2004, Sagar C Nannapaneni wrote: Hi all, I have an ID field in my database...it reads like this ASS1 ASS23 ASS4 ASS10 ASS6 . . . when i'm retrieving the data by taking ORDER BY clause it is sorting like this ASS1 ASS10 ASS23 ASS4 ASS6 means its only sorting by the 4 the character. i want the sorting to be done like the following ASS1 ASS4 ASS6 ASS10 ASS23 Solutions are greatly appreciated Sagar, You can also try: select * from table order by cast(substr(id,4) as unsigned) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by problem
The following might help, but will certainly be quite slow: SELECT ... ORDER BY ABS(SUBSTRING(field, 4)); A better (and faster) solution will probably be indexing the records with a numeric field, as usual. Fred, Doesn't MySQL always physically sort the rows and not use the index to obtain row order? Do you (or anyone else listening) know of a way to get MySQL to use the index for sorting instead of physically sorting the rows? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: order by problem with 3.22
At 20:43 +0100 2/16/03, sascha mantscheff wrote: The following query works with mysql 3.23: SELECT * FROM answer ORDER BY concat( n_sort, -, id_answer ) It does not with mysql 3.22.27. Neither does any query with a function call in the order by clause. Is this documented somewhere? Am I missing something? Is there a workaround other than upgrading to 3.23? Functions in ORDER BY are allowable as of MySQL 3.23.2: http://www.mysql.com/doc/en/News-3.23.2.html Prior to that, the workaround is to include the expression in the output column list, alias it, and refer to the alias in the ORDER BY clause: SELECT *, concat( n_sort, -, id_answer ) AS expr FROM answer ORDER BY expr; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: order by problem with 3.22
Am Sonntag, 16. Februar 2003 21:15 schrieb Paul DuBois: At 20:43 +0100 2/16/03, sascha mantscheff wrote: The following query works with mysql 3.23: SELECT * FROM answer ORDER BY concat( n_sort, -, id_answer ) It does not with mysql 3.22.27. Neither does any query with a function call in the order by clause. Is this documented somewhere? Am I missing something? Is there a workaround other than upgrading to 3.23? Functions in ORDER BY are allowable as of MySQL 3.23.2: http://www.mysql.com/doc/en/News-3.23.2.html Prior to that, the workaround is to include the expression in the output column list, alias it, and refer to the alias in the ORDER BY clause: SELECT *, concat( n_sort, -, id_answer ) AS expr FROM answer ORDER BY expr; thanks a lot, this does it. s.m. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: order by problem
assuming you have a table with two columns id and town then here's one solution: Create temporary table address (ad varchar(30)); Insert into address select concat(id, ' ', town) from your_original_table_name; select * from address order by ad; --- Nicolas JOURDEN [EMAIL PROTECTED] wrote: Hi, How can I fix an order by using numbers and letters ? Id Town 56 Paris 1 60 Paris 10 7 Paris 11 262 Paris 12 8 Paris 13 16 Paris 14 22 Paris 15 6 Paris 3 57 Paris 4 51 Paris 6 5 Paris 7 61 Paris 8 59 Paris 9 I'd like to get : 56 Paris 1 6 Paris 3 57 Paris 4 51 Paris 6 5 Paris 7 61 Paris 8 59 Paris 9 60 Paris 10 7 Paris 11 262 Paris 12 8 Paris 13 16 Paris 14 22 Paris 15 A this time I'm doing this sql syntax : SELECT * FROM town ORDER BY town ASC How can I do it ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Order by - problem with numerics in varchar field
[snip] I'm having trouble with ordering. I've got data in a varchar field that currently gets ordered like this when I use 'order by myfield asc': aristo 1001 aristo 156 aristo 222 I'd like it to order like this: aristo 156 aristo 222 aristo 1001 How can I do this in MySQL? Is there a way to take the numbers into account when using order by? [/snip] SELECT columnName, RIGHT(columnName, 4) AS theNumber FROM tableName ORDER BY theNumber +-+---+ | columnName | theNumber | +-+---+ | aristo 156 | 156 | | aristo 222 | 222 | | aristo 1001 | 1001 | | aristo 2317 | 2317 | +-+---+ Now, this will only work if you specify the correct number in the RIGHT() function. Since you had 4 digit numbers, I used 4. But if this number is longer, you need to increase your integer in the RIGHT() function as it will not be able to determine the length of the number. mysql select info AS columnName, SUBSTRING_INDEX(info, , -1) AS theNumber from tblStuff ORDER BY theNumber; +-+---+ | columnName | theNumber | +-+---+ | aristo 1001 | 1001 | | aristo 156 | 156 | | aristo 222 | 222 | | aristo 2317 | 2317 | +-+---+ As you can see, the SUBSTRING_INDEX() function retrieves the number after the space, but the query does not sort this the way that you want. RIGHT() takes into account the space before the 3 digit integer and sorts numbers with spaces first, which works for you in this case. The problem with these solutions is that they may not work in every case needed. HTH! Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Order by - problem with numerics in varchar field
On 11 Jul 2002, at 16:06, Dan Lamb wrote: I'd like it to order like this: aristo 156 aristo 222 aristo 1001 How can I do this in MySQL? Is there a way to take the numbers into account when using order by? There are various ways to break up your strings and convert part to a number using MySQL SQL, depending on what assumptions we're allowed to make about the format of your strings and the size of your numbers. Jay Blanchard has posted one possibility. However, your sorting will be much faster (and can use indexes better) if you don't have to do such calculations for each row every time you want to sort. If you need to sort by a two-part key, then you really should split the key into two columns, make one VARCHAR and one SMALLINT (or whatever), and make an index on both. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ORDER BY problem and possibly others..
* J.M. Roth [EMAIL PROTECTED] wrote on 28.01.01 02:05: I just installed the newest MySQL (3.23.32) with PHP 4.0.4pl1 (shared module) on an Apache 1.3.12 (Linux). ... $query = "SELECT * FROM $userstable ORDER BY when DESC LIMIT 0, 3"; doesn't work: From your query I think you upgraded from a 3.22.x version? When was not a reserved word in 3.22, but is so in 3.23. There have been a few hints how to both circumvent and fix this in 3.23, just search the archives. The thread was called something like "3.22 databases in 3.23 cause problems with fields named 'when'". -- Tomi Junnila [EMAIL PROTECTED] http://www.badzilla.net/~topeju/ Electronics and Information Technology, University of Turku, Finland - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ORDER BY problem and possibly others..
"J.M. Roth" wrote: Hello, I just installed the newest MySQL (3.23.32) with PHP 4.0.4pl1 (shared module) on an Apache 1.3.12 (Linux). Some SQL syntaxes that worked before don't anymore. E.g.: $query = "SELECT * FROM $userstable ORDER BY when DESC LIMIT 0, 3"; doesn't work: Warning: Supplied argument is not a valid MySQL result resource in /home/FV/aal/public_html/frame1.php3 on line 168 empty set http://www.mysql.com/doc/R/e/Reserved_words.html http://www.mysql.com/doc/N/e/News-3.23.2.html ... sorry, somtimes adding new features ads reserved words - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
