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]
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]
order desc problem
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)) {
?
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]
strange order by problem
I need to order a few names by the number following
the main name. For example swap2, swap3, swap10 in the
order of swap2, swap3, swap10, not in swap10, swap2,
swap3 as it will happen when I do an order by.
So I came up with the following query:
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname,lengt
h(secname)-locate('p',secname))+0), secname);
I was hoping it will order by the number following
each 'swap' in the secname, it doesn't work. It was
ordered instead by secname.
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-23 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-23 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-21 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-21 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
+--++
However, if I replace the second expression in the if
statement by date, like the following, it's ordered by
date as I would expect.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',date, secname);
+--++
| secname | date |
+--++
| SWAP3| 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP5| 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-21 |
| SWAP0.25 | 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-22 |
| SWAP10 | 2005-09-23 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP10 | 2005-09-26 |
| SWAP2| 2005-09-26 |
| SWAP3| 2005-09-26 |
| SWAP5| 2005-09-26 |
+--++
So I tried different combinations of the second and
third expressions in the if statement in the query,
the next one is the only one I can get it to order my
way, which is not what I wanted of course since I
don't want other secnames than swap% to order this
way.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname, leng
th(secname)-locate('p', secname))+0),
right(secname,length(secname)-locate('p',secname))+0);
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-21 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-21 |
| SWAP10 | 2005-09-23 |
+--++
Can anyone see what problems I have in my query? I'm
really stuck here. Thanks.
Claire
__
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: strange order by problem
Try this
mysql select distinct secname, date
- from optresult
- where secname like 'swap%'
-and date like '2005-09-2%'
- order by if(secname like 'swap%',
- (mid(secname,5,20)+0),
- secname);
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-23 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-23 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-21 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP3| 2005-09-21 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
| SWAP5| 2005-09-21 |
| SWAP5| 2005-09-26 |
+--++
18 rows in set (0.00 sec)
-Original Message-
From: Claire Lee [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 27, 2005 2:48 PM
To: mysql@lists.mysql.com
Subject: strange order by problem
I need to order a few names by the number following
the main name. For example swap2, swap3, swap10 in the
order of swap2, swap3, swap10, not in swap10, swap2,
swap3 as it will happen when I do an order by.
So I came up with the following query:
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname,lengt
h(secname)-locate('p',secname))+0), secname);
I was hoping it will order by the number following
each 'swap' in the secname, it doesn't work. It was
ordered instead by secname.
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-23 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-23 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-21 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-21 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
+--++
However, if I replace the second expression in the if
statement by date, like the following, it's ordered by
date as I would expect.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',date, secname);
+--++
| secname | date |
+--++
| SWAP3| 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP5| 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-21 |
| SWAP0.25 | 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-22 |
| SWAP10 | 2005-09-23 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP10 | 2005-09-26 |
| SWAP2| 2005-09-26 |
| SWAP3| 2005-09-26 |
| SWAP5| 2005-09-26 |
+--++
So I tried different combinations of the second and
third expressions in the if statement in the query,
the next one is the only one I can get it to order my
way, which is not what I wanted of course since I
don't want other secnames than swap% to order this
way.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname, leng
th(secname)-locate('p', secname))+0),
right(secname,length(secname)-locate('p',secname))+0);
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-21 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-21 |
| SWAP10 | 2005-09-23 |
+--++
Can anyone see what problems I have in my query? I'm
really stuck here. Thanks.
Claire
__
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
--
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: strange order by problem
Claire Lee [EMAIL PROTECTED] wrote on 09/27/2005 03:48:11 PM:
I need to order a few names by the number following
the main name. For example swap2, swap3, swap10 in the
order of swap2, swap3, swap10, not in swap10, swap2,
swap3 as it will happen when I do an order by.
So I came up with the following query:
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname,lengt
h(secname)-locate('p',secname))+0), secname);
I was hoping it will order by the number following
each 'swap' in the secname, it doesn't work. It was
ordered instead by secname.
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-23 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-23 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-21 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-21 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
+--++
However, if I replace the second expression in the if
statement by date, like the following, it's ordered by
date as I would expect.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',date, secname);
+--++
| secname | date |
+--++
| SWAP3| 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP5| 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-21 |
| SWAP0.25 | 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-22 |
| SWAP10 | 2005-09-23 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP10 | 2005-09-26 |
| SWAP2| 2005-09-26 |
| SWAP3| 2005-09-26 |
| SWAP5| 2005-09-26 |
+--++
So I tried different combinations of the second and
third expressions in the if statement in the query,
the next one is the only one I can get it to order my
way, which is not what I wanted of course since I
don't want other secnames than swap% to order this
way.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname, leng
th(secname)-locate('p', secname))+0),
right(secname,length(secname)-locate('p',secname))+0);
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-21 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-21 |
| SWAP10 | 2005-09-23 |
+--++
Can anyone see what problems I have in my query? I'm
really stuck here. Thanks.
Claire
So you want to sort by secname except when secname starts with 'SWAP'
ORDER BY secname
, if (secname like 'swap%'
,(right(secname, length(secname)-locate('p', secname))+0)
,0)
, date;
by giving every *other* entry a default second sort-by of 0, they end up
all sorting according to secname then date. It's when secname starts with
swap that you get the sub-sorting value according to the end of the
string. Make sense?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: strange order by problem
Claire Lee wrote: I need to order a few names by the number following the main name. For example swap2, swap3, swap10 in the order of swap2, swap3, swap10, not in swap10, swap2, swap3 as it will happen when I do an order by. ... ORDER BY ABS(SUBSTRING(secname,5)) ... will insure that the trailing digits are treated as numbers :-) +--++ | secname | date | +--++ | SWAP0.25 | 2005-09-21 | | SWAP0.5 | 2005-09-21 | | SWAP1| 2005-09-21 | | SWAP10 | 2005-09-26 | | SWAP10 | 2005-09-23 | | SWAP10 | 2005-09-21 | | SWAP2| 2005-09-26 | | SWAP2| 2005-09-23 | | SWAP2| 2005-09-22 | | SWAP2| 2005-09-21 | | SWAP3| 2005-09-21 | | SWAP3| 2005-09-26 | | SWAP3| 2005-09-23 | | SWAP3| 2005-09-22 | | SWAP5| 2005-09-21 | | SWAP5| 2005-09-26 | | SWAP5| 2005-09-23 | | SWAP5| 2005-09-22 | +--++ HTH, -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: strange order by problem
[EMAIL PROTECTED] wrote:
Claire Lee [EMAIL PROTECTED] wrote on 09/27/2005 03:48:11 PM:
I need to order a few names by the number following
the main name. For example swap2, swap3, swap10 in the
order of swap2, swap3, swap10, not in swap10, swap2,
swap3 as it will happen when I do an order by.
So I came up with the following query:
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname,lengt
h(secname)-locate('p',secname))+0), secname);
I was hoping it will order by the number following
each 'swap' in the secname, it doesn't work. It was
ordered instead by secname.
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-23 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-23 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-21 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-21 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
+--++
However, if I replace the second expression in the if
statement by date, like the following, it's ordered by
date as I would expect.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',date, secname);
+--++
| secname | date |
+--++
| SWAP3| 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP5| 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-21 |
| SWAP0.25 | 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-22 |
| SWAP10 | 2005-09-23 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP10 | 2005-09-26 |
| SWAP2| 2005-09-26 |
| SWAP3| 2005-09-26 |
| SWAP5| 2005-09-26 |
+--++
So I tried different combinations of the second and
third expressions in the if statement in the query,
the next one is the only one I can get it to order my
way, which is not what I wanted of course since I
don't want other secnames than swap% to order this
way.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname, leng
th(secname)-locate('p', secname))+0),
right(secname,length(secname)-locate('p',secname))+0);
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-21 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-21 |
| SWAP10 | 2005-09-23 |
+--++
Can anyone see what problems I have in my query? I'm
really stuck here. Thanks.
Claire
So you want to sort by secname except when secname starts with 'SWAP'
ORDER BY secname
, if (secname like 'swap%'
,(right(secname, length(secname)-locate('p', secname))+0)
,0)
, date;
by giving every *other* entry a default second sort-by of 0, they end up
all sorting according to secname then date. It's when secname starts with
swap that you get the sub-sorting value according to the end of the
string. Make sense?
If secname is like 'swap%', why are you then using locate to find the p
when it has to be the 4th letter or secname wouldn't be like 'swap%'.
Also if your first order by argument is secname how is the second
argument going to do anything since swap10 and swap2 are different the
first argument is all you need to uniquely identify them.
--
Chris W
Gift Giving Made Easy
Get the gifts you want
give the gifts they want
One stop wish list for any gift,
from anywhere, for any occasion!
http://thewishzone.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: strange order by problem
Claire Lee wrote: This simplified my second expression in the if statement. Thank you. But the query still doesn't sort by the numbers, here's the result: mysql select distinct secname, date from optresult where secname like 'swap%' a nd date like '2005-09-2%' order by if (secname like 'swap%',abs(substring(secnam e,5)), secname); SELECT DISTINCT secname , date FROM optresult WHERE secname LIKE 'swap%' AND date LIKE '2005-09-2%' ORDER BY ABS(SUBSTRING(secname,5)) You don't need the IF in the ORDER BY -- that's already been constrained by your WHERE ... +--++ | secname | date | +--++ | SWAP0.25 | 2005-09-21 | | SWAP0.5 | 2005-09-21 | | SWAP1| 2005-09-21 | | SWAP10 | 2005-09-26 | | SWAP10 | 2005-09-23 | | SWAP10 | 2005-09-21 | | SWAP2| 2005-09-26 | | SWAP2| 2005-09-23 | | SWAP2| 2005-09-22 | | SWAP2| 2005-09-21 | | SWAP3| 2005-09-21 | | SWAP3| 2005-09-26 | | SWAP3| 2005-09-23 | | SWAP3| 2005-09-22 | | SWAP5| 2005-09-21 | | SWAP5| 2005-09-26 | | SWAP5| 2005-09-23 | | SWAP5| 2005-09-22 | +--++ HTH! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER BY problem with JOINs
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? ...Rene -- 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]
mySQL LIMIT and ORDER BY Problem???
Hi there, I recently learned about LIMIT so that I can page through records on a web page I am creating. However, when coupled with ORDER BY, the sorting does not work anymore. Can someone look at my below query and point out what I am doing wrong? Thanks. This is my first time posting to this list, so I hope I have the right place. I want to sort by the client's last name and then by the staff person's last name. $qClients = SELECT * FROM clients, staff WHERE clients.AssignedTo = staff.StaffID ORDER BY clients.LastName, staff.StaffLastName ASC LIMIT . $limitStart . , . $recordsPerPage; Gary
Re: mySQL LIMIT and ORDER BY Problem???
On Sunday 04 July 2004 02:52 am, Gary Mack wrote: Hi there, I recently learned about LIMIT so that I can page through records on a web page I am creating. However, when coupled with ORDER BY, the sorting does not work anymore. Can someone look at my below query and point out what I am doing wrong? Thanks. This is my first time posting to this list, so I hope I have the right place. I want to sort by the client's last name and then by the staff person's last name. $qClients = SELECT * FROM clients, staff WHERE clients.AssignedTo = staff.StaffID ORDER BY clients.LastName, staff.StaffLastName ASC LIMIT . $limitStart . , . $recordsPerPage; Gary Looks good to me. Can you be more specific about what the problem is? --John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RES: ORDER BY problem
On Tue, 8 Jun 2004, Renato Cramer wrote: Hello Andy, I don't known if this is possible without handle string, what I don't guess recommended, because of performance and legibility of code. One suggestion will be store in column 'surname' (or other) the data already in format of sort. In other words, will be two columns in table, and, depending on approach, the second column will can be disabled for the users. Example: Name: Marco van Basten Archive: Basten, Marco van I hope that helps. 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. Thanks for your suggestion anyway. Andy -Mensagem original- De: andy thomas [mailto:[EMAIL PROTECTED] Enviada em: terça-feira, 8 de junho de 2004 08:51 Para: [EMAIL PROTECTED] Assunto: ORDER BY problem In a table called 'advisers' I have a column called 'surname' which contains the surnames of a number of people. Using a query like: 'select * from advisers order by surname' lists the people in the correct order but some people have surnames like 'du Sautoy' and 'van den Berg' and these are listed in the order of the first character that appears in their name, so that 'du Sautoy' appears surnames beginning with 'D' rather than 'S', etc. Does anyone know of a way of getting ORDER BY to sort on uppercase elements only in a sort string, so that 'du' and 'van den' in the example above are effectively ignored? Andy # 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: AW: ORDER BY problem
On Tue, 8 Jun 2004, Franz, Fa. PostDirekt MA wrote: Hi, it is not possible to handle all cases proper. You can just handle all cases you know with the REPLACE-function, so you simply delete the prefixes in the WHERE-clause. But that only works for all prefixes you know. If you do like ORDER BY REPLACE(REPLACE(surname,'du',''),'de','') you get all 'du Sautoy' and 'de Contes' exactly like you wanted it but 'de la Tour' will still apear at the wrong place. I don't think you can be sure to remove all prefixes like this, because you can't be sure to know all of them. A different trick would be to say allways take the last 'word' in the surname, which is much more efficent, but will unfortunally not work with double names like 'Schwarzenegger Schriver' (Maybe they are written with a '-' in it, it's just to show the principle). This is what I did in the end, to alwyas use the last word that's separated by a space. So, there is not lot the world can learn from germany, but we treat all these prefixes like they belong ti the name, which means 'von Hohenzollern' is correctly ordered among the the v's and not the h's. Well, we have run into this problem already since I 'fixed' the ordering! As there are people from all over the world using this database, we are now about to redesign the table to allow individual people to decide where they want their surname to appear in the listing. Thanks for your suggestions, Andy -Ursprüngliche Nachricht- Von: andy thomas [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 8. Juni 2004 13:51 An: [EMAIL PROTECTED] Betreff: ORDER BY problem In a table called 'advisers' I have a column called 'surname' which contains the surnames of a number of people. Using a query like: 'select * from advisers order by surname' lists the people in the correct order but some people have surnames like 'du Sautoy' and 'van den Berg' and these are listed in the order of the first character that appears in their name, so that 'du Sautoy' appears surnames beginning with 'D' rather than 'S', etc. Does anyone know of a way of getting ORDER BY to sort on uppercase elements only in a sort string, so that 'du' and 'van den' in the example above are effectively ignored? Andy -- 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, 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]
ORDER BY problem
In a table called 'advisers' I have a column called 'surname' which contains the surnames of a number of people. Using a query like: 'select * from advisers order by surname' lists the people in the correct order but some people have surnames like 'du Sautoy' and 'van den Berg' and these are listed in the order of the first character that appears in their name, so that 'du Sautoy' appears surnames beginning with 'D' rather than 'S', etc. Does anyone know of a way of getting ORDER BY to sort on uppercase elements only in a sort string, so that 'du' and 'van den' in the example above are effectively ignored? Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RES: ORDER BY problem
Hello Andy, I don't known if this is possible without handle string, what I don't guess recommended, because of performance and legibility of code. One suggestion will be store in column 'surname' (or other) the data already in format of sort. In other words, will be two columns in table, and, depending on approach, the second column will can be disabled for the users. Example: Name: Marco van Basten Archive: Basten, Marco van I hope that helps. Regards, Renato Cramer. -Mensagem original- De: andy thomas [mailto:[EMAIL PROTECTED] Enviada em: terça-feira, 8 de junho de 2004 08:51 Para: [EMAIL PROTECTED] Assunto: ORDER BY problem In a table called 'advisers' I have a column called 'surname' which contains the surnames of a number of people. Using a query like: 'select * from advisers order by surname' lists the people in the correct order but some people have surnames like 'du Sautoy' and 'van den Berg' and these are listed in the order of the first character that appears in their name, so that 'du Sautoy' appears surnames beginning with 'D' rather than 'S', etc. Does anyone know of a way of getting ORDER BY to sort on uppercase elements only in a sort string, so that 'du' and 'van den' in the example above are effectively ignored? Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: ORDER BY problem
Hi, it is not possible to handle all cases proper. You can just handle all cases you know with the REPLACE-function, so you simply delete the prefixes in the WHERE-clause. But that only works for all prefixes you know. If you do like ORDER BY REPLACE(REPLACE(surname,'du',''),'de','') you get all 'du Sautoy' and 'de Contes' exactly like you wanted it but 'de la Tour' will still apear at the wrong place. I don't think you can be sure to remove all prefixes like this, because you can't be sure to know all of them. A different trick would be to say allways take the last 'word' in the surname, which is much more efficent, but will unfortunally not work with double names like 'Schwarzenegger Schriver' (Maybe they are written with a '-' in it, it's just to show the principle). So, there is not lot the world can learn from germany, but we treat all these prefixes like they belong ti the name, which means 'von Hohenzollern' is correctly ordered among the the v's and not the h's. prosit Klaus -Ursprüngliche Nachricht- Von: andy thomas [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 8. Juni 2004 13:51 An: [EMAIL PROTECTED] Betreff: ORDER BY problem In a table called 'advisers' I have a column called 'surname' which contains the surnames of a number of people. Using a query like: 'select * from advisers order by surname' lists the people in the correct order but some people have surnames like 'du Sautoy' and 'van den Berg' and these are listed in the order of the first character that appears in their name, so that 'du Sautoy' appears surnames beginning with 'D' rather than 'S', etc. Does anyone know of a way of getting ORDER BY to sort on uppercase elements only in a sort string, so that 'du' and 'van den' in the example above are effectively ignored? Andy -- 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
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]
Group By Order By problem
Hi all, I am trying to get a bunch of results, group them by category, and then order each group of categories. My query is thus: SELECT l.CatalogNumber, l.MP3Name, l.PDFLink, l.PDFName, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, o.Alias FROM listings l, publishers p, composers c, arrangers a, categories o WHERE (a.ArrangerLname like '%$Criteria%' or p.PublisherName like '%$Criteria%' or c.ComposerLname like '%$Criteria%' or l.Title like '%$Criteria%' or l.CatalogNumber like '%$Criteria%' or l.Price like '%$Criteria%' or l.Description like '%$Criteria%') AND l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID and l.CategoryID=o.CategoryID GROUP BY o.Alias ASC ORDER BY o.Alias, c.ComposerLname ASC; This only displays 1 row in each category, so clearly I have an error in my Group By and/or Order By clause(s). I am sure my error is fairly basic, but I don't have enough experience with MySQL to figure it out. Does anyone have any insight into the problem? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Order by problem
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 thanks in adavance /sagar
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: GROUP BY/ORDER BY Problem
The SQL specification does allow aggregates in the ORDER BY. Does mySQL have any plans to add such functionality (or at least add it to its list of things it doesn't do)? The problem with the solution of ordering by an alias is that I may not necessarily want the thing I'm ordering by to be in the result set. In the example below, I may just want to select the breed, ordered by minimum age, without showing the min. age. Is there a good way in mySQL to make this work? Thanks. Why doesn't the following work: mysql CREATE TABLE dog(id integer, breed char(20), age integer, weight integer) ; mysql SELECT breed, MIN(age) - FROM dog - GROUP BY breed - ORDER BY MIN(age); ERROR : Invalid use of group function I don't believe that aggregate functions are legal in an ORDER BY clause. The solution, as you've found, is to select the value you want to order by, alias it, and refer to the alias in the ORDER BY clause. but this does mysql SELECT breed, MIN(age) AS minage - FROM dog - GROUP BY breed - ORDER BY minage; -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY/ORDER BY Problem
At 5:52 -0700 10/3/03, Ed Smith wrote: Why doesn't the following work: mysql CREATE TABLE dog(id integer, breed char(20), age integer, weight integer) ; mysql SELECT breed, MIN(age) - FROM dog - GROUP BY breed - ORDER BY MIN(age); ERROR : Invalid use of group function I don't believe that aggregate functions are legal in an ORDER BY clause. The solution, as you've found, is to select the value you want to order by, alias it, and refer to the alias in the ORDER BY clause. but this does mysql SELECT breed, MIN(age) AS minage - FROM dog - GROUP BY breed - ORDER BY minage; -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP BY/ORDER BY Problem
Why doesn't the following work: mysql CREATE TABLE dog(id integer, breed char(20), age integer, weight integer) ; mysql SELECT breed, MIN(age) - FROM dog - GROUP BY breed - ORDER BY MIN(age); ERROR : Invalid use of group function but this does mysql SELECT breed, MIN(age) AS minage - FROM dog - GROUP BY breed - ORDER BY minage; __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
insert ... select .. order by, problem
Hallo everyone ! I already sent this, but I think some people think is not clear enough ;-) Im using Mysql 4.0.12 on RedHat 7.3 x86 I know it's not the last binary but I cannot upgrade now. (And i saw nothing about this in the changelog for 4.013 and 4.0.14) I found the following : I have two tables : Stock (InnoDB, primary key on d): a char (16) b char (20) c char (20) d int e decimal (9,2) h int i int PTemp (MyISAM, no keys): d int e decimal f int g char (1) And the statement I am using is : INSERT INTO PTemp SELECT d,e,32,'E' FROM Stock WHERE hi ORDER BY a,b,c; I am doing an insert/select with order by, in both cases I am using the same statemant. When I use the same statement in my application (built with C, and statically linked to libmysqlclient.a) I get the reversed order (the records that start with '0' are at the end). When I test the statement in the mysql cli and I get the results well sorted (the records that start with '0' are at the begining). I checked the log and both statements are equal, (but the two users used to access the DB are different, the mysql cli user is root, and the other just have enough permissions to select, update,delete and insert in the tables). I would like to know (if that is possible) what happens. Thanks in advance. Ale __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Hi, well, I'm not totally sure about your question. Which order is reversed ? The order you get the entries with a select after the insert ? If it is this, then I think it's not a problem with the insert. The order is then given by the select, and if no order by is in the select, it is arbitrary. Since the physical order of entries in your temp table isn't of any relevance, I can't see any point in using order by in this statement. Stefan Am Tuesday 02 September 2003 10:06 schrieb Alejandro Paz: Hallo everyone ! I already sent this, but I think some people think is not clear enough ;-) Im using Mysql 4.0.12 on RedHat 7.3 x86 I know it's not the last binary but I cannot upgrade now. (And i saw nothing about this in the changelog for 4.013 and 4.0.14) I found the following : I have two tables : Stock (InnoDB, primary key on d): a char (16) b char (20) c char (20) d int e decimal (9,2) h int i int PTemp (MyISAM, no keys): d int e decimal f int g char (1) And the statement I am using is : INSERT INTO PTemp SELECT d,e,32,'E' FROM Stock WHERE hi ORDER BY a,b,c; I am doing an insert/select with order by, in both cases I am using the same statemant. When I use the same statement in my application (built with C, and statically linked to libmysqlclient.a) I get the reversed order (the records that start with '0' are at the end). When I test the statement in the mysql cli and I get the results well sorted (the records that start with '0' are at the begining). I checked the log and both statements are equal, (but the two users used to access the DB are different, the mysql cli user is root, and the other just have enough permissions to select, update,delete and insert in the tables). I would like to know (if that is possible) what happens. Thanks in advance. Ale __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Hi Stephan, Let's see the case : I use ORDER BY, because I want that order in PTemp table, so I do not have to order them later (because they are retrieved several times later). 1. Inserting with mysql c.l.i. : I get the records well sorted : first by a, secondly by b and finally by c (ascendig order). I'm using d to relink both tables in a join. 2. Inserting with the application : I get the records well sorted : first by a, secondly by b and finally by c, but in descending order. As all records has the same value in a, so the records that in case 1 start with '0' are at the beginning, the same records here are at the end. I'm using d to relink both tables in a join. I thought that was clear when I said reverse order, sorry. So you see what I mean ? thanks ! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Hi Alejondro I use ORDER BY, because I want that order in PTemp table, so I do not have to order them later (because they are retrieved several times later). If I understand correct ,,, U can never trust the order ur records get return from DB (it is indepented of the order u insert records ) with other words , I u are interest in a specific order when selecting , u MUST use Order By hope this help regards Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Hi, I think you can't do this. There is no order in the table, so there is no point in using order by with insert. You always have to do this when retrieving the records (the order you get with select without order by is accidential). HTH Stefan Am Tuesday 02 September 2003 11:49 schrieb Alejandro Paz: Hi Stephan, Let's see the case : I use ORDER BY, because I want that order in PTemp table, so I do not have to order them later (because they are retrieved several times later). 1. Inserting with mysql c.l.i. : I get the records well sorted : first by a, secondly by b and finally by c (ascendig order). I'm using d to relink both tables in a join. 2. Inserting with the application : I get the records well sorted : first by a, secondly by b and finally by c, but in descending order. As all records has the same value in a, so the records that in case 1 start with '0' are at the beginning, the same records here are at the end. I'm using d to relink both tables in a join. I thought that was clear when I said reverse order, sorry. So you see what I mean ? thanks ! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Stefan, Do you imply that tables cannot be sorted desc or asc based on one of the columns e.g. a last name? or am I misunderstanding you. Albert Atlanta (anyone else in Atlanta?) - Original Message - From: Stefan Kuhn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 6:57 AM Subject: Re: insert ... select .. order by, problem Hi, I think you can't do this. There is no order in the table, so there is no point in using order by with insert. You always have to do this when retrieving the records (the order you get with select without order by is accidential). HTH Stefan Am Tuesday 02 September 2003 11:49 schrieb Alejandro Paz: Hi Stephan, Let's see the case : I use ORDER BY, because I want that order in PTemp table, so I do not have to order them later (because they are retrieved several times later). 1. Inserting with mysql c.l.i. : I get the records well sorted : first by a, secondly by b and finally by c (ascendig order). I'm using d to relink both tables in a join. 2. Inserting with the application : I get the records well sorted : first by a, secondly by b and finally by c, but in descending order. As all records has the same value in a, so the records that in case 1 start with '0' are at the beginning, the same records here are at the end. I'm using d to relink both tables in a join. I thought that was clear when I said reverse order, sorry. So you see what I mean ? thanks ! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- 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: insert ... select .. order by, problem
Hi Albert, you are not misunderstanding me :-) Tables can indeed not be sorted, it's output which gets sorted. The difference is not academic, but important: It's not the table which gets an order, but the output. Take a command like: insert into x ... select from y ... order by z. Here the output of select gets sorted and inserted into the table x. In this table, there is not order, so if you do then a select from x, the order is arbitrary again and you need to do select from x order by z. And this means you could have dropped the order by in insert totally. Hope it became clear. Stefan Am Tuesday 02 September 2003 13:20 schrieb Albert: Stefan, Do you imply that tables cannot be sorted desc or asc based on one of the columns e.g. a last name? or am I misunderstanding you. Albert Atlanta (anyone else in Atlanta?) - Original Message - From: Stefan Kuhn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 6:57 AM Subject: Re: insert ... select .. order by, problem Hi, I think you can't do this. There is no order in the table, so there is no point in using order by with insert. You always have to do this when retrieving the records (the order you get with select without order by is accidential). HTH Stefan Am Tuesday 02 September 2003 11:49 schrieb Alejandro Paz: Hi Stephan, Let's see the case : I use ORDER BY, because I want that order in PTemp table, so I do not have to order them later (because they are retrieved several times later). 1. Inserting with mysql c.l.i. : I get the records well sorted : first by a, secondly by b and finally by c (ascendig order). I'm using d to relink both tables in a join. 2. Inserting with the application : I get the records well sorted : first by a, secondly by b and finally by c, but in descending order. As all records has the same value in a, so the records that in case 1 start with '0' are at the beginning, the same records here are at the end. I'm using d to relink both tables in a join. I thought that was clear when I said reverse order, sorry. So you see what I mean ? thanks ! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
* Albert Stefan, I'm Roger, but I reply anyway. :) Do you imply that tables cannot be sorted desc or asc based on one of the columns e.g. a last name? or am I misunderstanding you. In relational database theory the order of rows within the table is undefined, i.e. it is up to the server, and the server can re-organize a table at any time. If you want an ordered result, you have to use ORDER BY in your SELECT statement. However, the MySQL server has some features that can be used to achieve exactly what you ask. It is possible to sort a table physically, in order to do faster reads later. The order is however destroyed if you do additional inserts. See the ORDER BY option of the ALTER TABLE statement: ORDER BY allows you to create the new table with the rows in a specific order. Note that the table will not remain in this order after inserts and deletes. In some cases, it may make sorting easier for MySQL if the table is in order by the column that you wish to order it by later. This option is mainly useful when you know that you are mostly going to query the rows in a certain order; by using this option after big changes to the table, you may be able to get higher performance. URL: http://www.mysql.com/doc/en/ALTER_TABLE.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Stefan, Indeed, and my mistake (semantics). I meant what you explained. It is clear to me that the order in the table remains in the manner the data were entered, and that cannot be changed, unless a record is deleted and then re-entered, which would place it elsewhere (at the end). This does not really have any benefits IMHO. And yes, I understand that it is the output that is sorted based on the query. Thanks for clarifying this, and as I read my question, I should have seen the difference myself. Mea culpa! : =) I am quite familiar with SQL Server 2000, but need to use MySQL for a project for the University I am at (Devry Alpharetta, Atlanta), to capture the input from a student survey of the classes and the Faculty members. There are several fields: semester (char), course(char), courseID (int), Faculty (varchar[30] - if that is acceptable in that format - and the answers to 18 questions, all alpha characters (char) or numeric char (int), and one Boolean (yes/no or 1,0). I need to figure out how to best structure this, e.g. create tables on the fly (if that is possible using ASP/ADO and SQL with ODBC connector), or create tables with many to many relationships and store the data for each course survey in a separate table. The tables with many to many relationships would hold all the courses, courseID's, and Faculty members, and the answers to the survey would create links between those and the results from the surveys. A typical class unique identification would look like this: sum03_FBaah_CIS_349 The cols would be 1 through 18 + a calculation col for the average of questions 1 to 18 and a col for the average of all answers to question 1, question 2, etc ... Mind you I may export the answers to an excel spreadsheet and do the calculations there rather than in the DB itself. Anyway this is a long answer to your response but I wanted those who read this to get an idea of what I am working with. Any suggestions are welcomed. Albert - Original Message - From: Stefan Kuhn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 8:10 AM Subject: Re: insert ... select .. order by, problem Hi Albert, you are not misunderstanding me :-) Tables can indeed not be sorted, it's output which gets sorted. The difference is not academic, but important: It's not the table which gets an order, but the output. Take a command like: insert into x ... select from y ... order by z. Here the output of select gets sorted and inserted into the table x. In this table, there is not order, so if you do then a select from x, the order is arbitrary again and you need to do select from x order by z. And this means you could have dropped the order by in insert totally. Hope it became clear. Stefan Am Tuesday 02 September 2003 13:20 schrieb Albert: Stefan, Do you imply that tables cannot be sorted desc or asc based on one of the columns e.g. a last name? or am I misunderstanding you. Albert Atlanta (anyone else in Atlanta?) - Original Message - From: Stefan Kuhn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 6:57 AM Subject: Re: insert ... select .. order by, problem Hi, I think you can't do this. There is no order in the table, so there is no point in using order by with insert. You always have to do this when retrieving the records (the order you get with select without order by is accidential). HTH Stefan Am Tuesday 02 September 2003 11:49 schrieb Alejandro Paz: Hi Stephan, Let's see the case : I use ORDER BY, because I want that order in PTemp table, so I do not have to order them later (because they are retrieved several times later). 1. Inserting with mysql c.l.i. : I get the records well sorted : first by a, secondly by b and finally by c (ascendig order). I'm using d to relink both tables in a join. 2. Inserting with the application : I get the records well sorted : first by a, secondly by b and finally by c, but in descending order. As all records has the same value in a, so the records that in case 1 start with '0' are at the beginning, the same records here are at the end. I'm using d to relink both tables in a join. I thought that was clear when I said reverse order, sorry. So you see what I mean ? thanks ! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL
Re: insert ... select .. order by, problem
Roger, Thanks for the additional clarification Albert Atlanta - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Albert [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 8:00 AM Subject: Re: insert ... select .. order by, problem * Albert Stefan, I'm Roger, but I reply anyway. :) Do you imply that tables cannot be sorted desc or asc based on one of the columns e.g. a last name? or am I misunderstanding you. In relational database theory the order of rows within the table is undefined, i.e. it is up to the server, and the server can re-organize a table at any time. If you want an ordered result, you have to use ORDER BY in your SELECT statement. However, the MySQL server has some features that can be used to achieve exactly what you ask. It is possible to sort a table physically, in order to do faster reads later. The order is however destroyed if you do additional inserts. See the ORDER BY option of the ALTER TABLE statement: ORDER BY allows you to create the new table with the rows in a specific order. Note that the table will not remain in this order after inserts and deletes. In some cases, it may make sorting easier for MySQL if the table is in order by the column that you wish to order it by later. This option is mainly useful when you know that you are mostly going to query the rows in a certain order; by using this option after big changes to the table, you may be able to get higher performance. URL: http://www.mysql.com/doc/en/ALTER_TABLE.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
order by problem with 3.22
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? - 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
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
order by problem
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
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
order by problem
hi, We noticed that a select statement using an order by that should return 0 rows fails on large table (~11 million rows), due to the following error: Out of sort memory. Increase daemon sort buffer size (becomes 3, Database error.) We searched the lists and found the following reference to this problem: http://www.bitmechanic.com/mail-archives/mysql/May1999/1044.html This says that mysql acquires enough memory to sort the entire table, which suggests that sorting is impractical on very large tables. Is this really the case? And, if so, shouldn't it be considered a bug? thanks for your help, joe sql query - 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
Order by - problem with numerics in varchar field
Hello All, 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? Thanks, Daniel Lamb - Original Message - From: Roma [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 11, 2002 3:12 PM Subject: Query : RE: How do i unsubscribe from the mysql list -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 11, 2002 12:01 PM To: Roma Subject: Re: How do i unsubscribe from the mysql list Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: Hi, I am just wondering, how can i unsubscribe from this list. I am sure, i can block this email address, but i would prefer to unsubscribe. anybody has the instructions. I would really appreciate that thanks roma - 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 - 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
join and order by problem
Hi, I have problem with join and order clause. I have 2 table join with amount condition and sort by order clause. The result of this join generate unwanted row to me. example table1 No. Amount Count 1 1000 2 2 2000 3 3500 1 table2 No. NameAmount 1 A 1000 2 B 2000 3 C 2000 4 D 1000 5 E500 6 F 2000 sql=select * from table1,table2 where table1.amount=table2.amount order by table2.amount desc,table2.no result table no nameamount count 2 B 20003 2 B 20003 3 C 20003 3 C 20003 6 C 20003 6 C 20003 1 A 10002 1 A 10002 4 D 10002 4 D 10002 5 E5001 5 E5001 If I did not use order by table2.no in sql it generate result correctly. SF - 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
ORDER BY problem and possibly others..
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 It seems it needs to be: $query = "SELECT * FROM $userstable ORDER BY 'when' DESC LIMIT 0, 3"; Also, phpMyAdmin is completely O3 (Out Of Order), the left pane usually showing the databases is only showing blanks Any comments? J.M. Roth J.M. Roth I N T E L L I G E N T - I P S . A . - Internet Services more PHONE: (+352) 26/4363-53FAX: (+352) 26/4363-73 OFFICES: 7, rue Pletzer Centre Helfent L-8080 Bertrange www.iip.lu - [EMAIL PROTECTED]MOBILE: (+352) 091/436353
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
