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

RE: ORDER BY problem

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

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

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

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

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

RE: strange order by problem

[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

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

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

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

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

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

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

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

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

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

Re: RES: ORDER BY problem

. 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

Re: AW: ORDER BY problem

: 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

RE: RE - Order By Problem

: 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

Re: RE - Order By Problem

- 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

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

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

RES: ORDER BY problem

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

AW: ORDER BY problem

, 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

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

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

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('

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

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

RE: RE - Order By Problem

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

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

Re: RE - Order By Problem

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

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,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Re: insert ... select .. order by, problem

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

Re: insert ... select .. order by, problem

[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

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

Re: insert ... select .. order by, problem

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

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

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

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

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

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

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:

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

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

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

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

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

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

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

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: