Hi,
Your query have to access all rows in `myTable`, thus MySQL optimizer
guesses reading sequentially is faster than working through an
index.
http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html
The case of not using index,
* Reading whole myTable.MYD sequentially
* Sorting 443k rows
Hi!
On Fri, Jun 13, 2014 at 3:59 AM, yoku ts. yoku0...@gmail.com wrote:
Hi,
Would you try STRAIGHT_JOIN?
mysql56 ALTER TABLE masik DROP KEY idx_test, ADD KEY idx_test(szam, id);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql56 EXPLAIN SELECT e.id FROM
Hi Lay,
If I don't mistake, you can't eliminate Using temporary and Using
filesort because you are using an order by. Try the explain again
removing order by and check the output.
When you use an order by, MySQL needs to use filesort and spends some time
sorting the result set. Also, create a
Hi!
On Thu, Jun 12, 2014 at 1:36 PM, Antonio Fernández Pérez
antoniofernan...@fabergames.com wrote:
Hi Lay,
If I don't mistake, you can't eliminate Using temporary and Using
filesort because you are using an order by. Try the explain again
removing order by and check the output.
Thank you,
Hi,
Would you try STRAIGHT_JOIN?
mysql56 ALTER TABLE masik DROP KEY idx_test, ADD KEY idx_test(szam, id);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql56 EXPLAIN SELECT e.id FROM masik m STRAIGHT_JOIN egyik e ON e.id=
m.id WHERE e.duma= 'aaa' ORDER BY m.szam
On Tue, Nov 9, 2010 at 10:09 AM, Mark Goodge m...@good-stuff.co.uk wrote:
Hi,
I have a query like this:
select id, title from product where id in (1,3,5,8,10)
What I want it to do is return the rows in the order specified in the in
clause, so that this:
select * from product where id in
-Original Message-
From: Joeri De Backer [mailto:fons...@gmail.com]
Sent: Tuesday, November 09, 2010 1:16 AM
To: mysql
Subject: Re: Order by in clause
On Tue, Nov 9, 2010 at 10:09 AM, Mark Goodge
m...@good-stuff.co.uk wrote:
Hi,
I have a query like this:
select id
It is inherent in your naming.
As long as your alias time is the same as the column name time, MySQL
will have no way to distinguish which one you refers to exactly in your
order-by clause, and chooses the alias in the select-clause as the one you
intended. You confused MySQL.
First, why you have
Easy.
SELECT DATE_FORMAT(`Time`, '%h:%i%p') as `Time_Format`
FROM `reservation`
ORDER BY `Time`
-Original Message-
From: BMBasal [mailto:bmb37...@gmail.com]
Sent: Wednesday, September 29, 2010 3:50 PM
To: 'Chris W'; 'MYSQL General List'
Subject: RE: ORDER BY with field alias issue
Order by reservation.time
JW
On Tuesday, September 28, 2010, Chris W 4rfv...@cox.net wrote:
I have the following query that is giving me problems.
SELECT DATE_FORMAT(`Time`, '%h:%i%p') as `Time`
FROM `reservation`
ORDER BY `Time`
Problem is it sorts wrong because of the date format
Hi,
With the following query if I it returns 2 results it's fast .04s, if
it has less results than the limit it takes 1minute.
Query:
select * from hub_dailies_sp where active='1' and date='2010-08-04'
order by id desc LIMIT 2;
Show create table:
http://pastebin.org/447171
27,000
Isn't it so that it firstly order the rows by id (index'ed?) and then scan
it to pick the rows which satisfy the where clause?
It stops when the result reaches the limit, otherwise scans the whole (27,
000 rows scan).
Then the response time with 2 rows limit by 2 can really depend. If the
Because you are sorting the results, the LIMIT clause has to be applied after
all of the eligible rows have been retrieved. There shouldn't be a big
difference between 2 and 3, but there would be between 2 and 2.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
But I'd prefer not to see the extra sorting field.
You don't need to select a field in order to be able to order by it.
So
select chart_of_accounts.accountname as Account,
concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as
Debit,
Keith Clark skrev:
I have the following statement:
select chart_of_accounts.accountname as Account,
concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as
Debit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0),2)) as
Credit,
On Wed, 2010-04-28 at 00:18 +0200, Carsten Pedersen wrote:
Keith Clark skrev:
I have the following statement:
select chart_of_accounts.accountname as Account,
concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as
Debit,
Try
order by CAST(Balance as decimal(8,2)) asc;
Cast will work in the order by.
Glenn Vaughn
- Original Message -
From: Keith Clark keithcl...@k-wbookworm.com
To: mysql@lists.mysql.com
Sent: Tuesday, April 27, 2010 3:52 PM
Subject: order by numeric value
I have the following
: Tuesday, April 27, 2010 3:46 PM
To: Keith Clark; mysql@lists.mysql.com
Subject: Re: order by numeric value
Try
order by CAST(Balance as decimal(8,2)) asc;
Cast will work in the order by.
Glenn Vaughn
- Original Message -
From: Keith Clark keithcl...@k-wbookworm.com
To: mysql
without the dollar
sign and order on that column.
Regards,
Gavin Towey
-Original Message-
From: DaWiz [mailto:da...@dawiz.net]
Sent: Tuesday, April 27, 2010 3:46 PM
To: Keith Clark; mysql@lists.mysql.com
Subject: Re: order by numeric value
Try
order by CAST(Balance
On Wed, 28 Apr 2010 08:53:57 Keith Clark wrote:
But I'd prefer not to see the extra sorting field.
You don't need to select a field in order to be able to order by it.
So
select chart_of_accounts.accountname as Account,
concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as
On Wed, 2010-04-28 at 08:57 +1000, Jesper Wisborg Krogh wrote:
On Wed, 28 Apr 2010 08:53:57 Keith Clark wrote:
But I'd prefer not to see the extra sorting field.
You don't need to select a field in order to be able to order by it.
So
select chart_of_accounts.accountname as Account,
SELECT ProductName FROM Products
WHERE ProductScore 100
ORDER BY CASE WHEN ProductScore = 125
THEN 0
ELSE 1
END, ProductScore
But this query won't use an index, so it would be a good idea to do this in
two queries
2008/10/24 Tompkins Neil [EMAIL PROTECTED]
Hi
I've the following basic
Following on from my email below I now need help with the following
problem. Here is a list of my sample data
Date ProductID ProductNameProductScore
Quantity
2008-11-10100 Red Light
0.05 10
2008-11-11100
SELECT ProductID,
ProductName,
AVG(ProductScore * Quantity) AS a
FROM Products
GROUP BY ProductID
ORDER BY a DESC
2008/10/24, Tompkins Neil [EMAIL PROTECTED]:
Following on from my email below I now need help with the following
problem. Here is a list of my sample data
Date
Hi
This works, however I still want to be able to list the whole list like
because I need to display it on the screen, but in the ordered together i.e
all RedLights, all BlueLights etc
a Date ProductID ProductName
ProductScore Quantity
%
Could give us sample values for a field? Should it contain the same thing
as in the query I've sent?
2008/10/24, Tompkins Neil [EMAIL PROTECTED]:
Hi
This works, however I still want to be able to list the whole list like
because I need to display it on the screen, but in the ordered together
Hi
Thanks for your quick reply. The sample value for a would be like a
average of integer. e.g 6.01, or 10.19.
Neil
On Fri, Oct 24, 2008 at 2:49 PM, Olexandr Melnyk [EMAIL PROTECTED] wrote:
Could give us sample values for a field? Should it contain the same thing
as in the query I've sent?
Still doesn't make much sense to me. Could you show us how to calculate it
for some of the rows above?
2008/10/24, Tompkins Neil [EMAIL PROTECTED]:
Hi
Thanks for your quick reply. The sample value for a would be like a
average of integer. e.g 6.01, or 10.19.
Neil
On Fri, Oct 24, 2008 at
Hi
Basically from the query below, it would only return one product like
RedLight. But I need to return a list of all products, ordered by a
SELECT ProductID,
ProductName,
AVG(ProductScore * Quantity) AS a
FROM Products
GROUP BY ProductID
ORDER BY a DESC
On Fri, Oct 24, 2008 at 2:53 PM,
See the usage of the function named field.
On Mon, Jul 28, 2008 at 8:15 PM, Mr. Shawn H. Corey [EMAIL PROTECTED]wrote:
On Mon, 2008-07-28 at 07:32 -0400, Gary Josack wrote:
Andrew Martin wrote:
Hello,
Is it permissible to order a clause such that the search term is the
first item
Andrew Martin wrote:
Hello,
Is it permissible to order a clause such that the search term is the
first item (in the clause)?
standard:
field1 IN (123, 654, 789)
in question:
123 IN (field1, field2, field3)
I am interested to know if the optimizer treats this any differently
if anybody can
On Mon, 2008-07-28 at 07:32 -0400, Gary Josack wrote:
Andrew Martin wrote:
Hello,
Is it permissible to order a clause such that the search term is the
first item (in the clause)?
standard:
field1 IN (123, 654, 789)
in question:
123 IN (field1, field2, field3)
I am
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
- 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
That is fine.
On Thu, May 8, 2008 at 4:51 PM, Neil Tompkins [EMAIL PROTECTED]
wrote:
Perfect. It worked just how I wanted.
Thanks for your help.
Neil
Date: Wed, 7 May 2008 19:54:39 +0200 To: [EMAIL PROTECTED]
Subject: Re: Order Problem From: [EMAIL PROTECTED] Hi, You should
look
Perfect. It worked just how I wanted.
Thanks for your help.
Neil
Date: Wed, 7 May 2008 19:54:39 +0200 To: [EMAIL PROTECTED] Subject: Re:
Order Problem From: [EMAIL PROTECTED] Hi, You should look at the
`FIND_IN_SET` function here:
http://dev.mysql.com/doc/refman/5.0/en/string
Neil Tompkins schrieb:
Hi,
How do I achieve a SQL statement to order my results based on two calculated fields for example :
what two calculated fields?
SELECT COUNT(ProductsPurchases.ProductID) as varProductCount, Products.Name,
Products.ProductReview
FROM ProductsPurchasesINNER JOIN
Hi
I want to order by the totalled fields varProductCount and
Products.ProductReviewDESC
Neil
Date: Thu, 20 Mar 2008 11:36:30 +0100 From: [EMAIL PROTECTED] To:
mysql@lists.mysql.com Subject: Re: ORDER BY calculated field Neil
Tompkins schrieb: Hi,How do I achieve a SQL statement
Neil Tompkins schrieb:
Hi
I want to order by the totalled fields varProductCount and Products.ProductReviewDESC
just put them together, separated with comma, like it is written in the manual
ORDER BY varProductCount + Products.ProductReviewDESC,
COUNT(ProductsPurchases.ProductID)
--
MySQL
Sebastian Mendel schrieb:
Neil Tompkins schrieb:
Hi
I want to order by the totalled fields varProductCount and
Products.ProductReviewDESC
just put them together, separated with comma, like it is written in the
manual
ORDER BY varProductCount + Products.ProductReviewDESC,
Neil Tompkins schrieb:
Thanks Sebastian, but I now get the error message
[MySQL][ODBC 3.51 Driver][mysqld-3.23.58]Invalid use of group function
i am not familiar with ODBC or MySQL 3.x
but possible just GROUP BY is missing
check the manual for your mysql version for the exact syntax
if
Thanks Sebastian, but I now get the error message
[MySQL][ODBC 3.51 Driver][mysqld-3.23.58]Invalid use of group function
Date: Thu, 20 Mar 2008 12:59:22 +0100 From: [EMAIL PROTECTED] To:
mysql@lists.mysql.com Subject: Re: ORDER BY calculated field Sebastian
Mendel schrieb: Neil
=
ProductsPurchases.ProductIDGROUP BY Products.ProductID ORDER BY varProductCount
DESC
Date: Thu, 20 Mar 2008 13:08:51 +0100 From: [EMAIL PROTECTED] CC:
mysql@lists.mysql.com Subject: Re: ORDER BY calculated field Neil
Tompkins schrieb: Thanks Sebastian, but I now get the error message
[MySQL][ODBC 3.51
Hello i use this query:
select i.item_id
from orders o
INNER JOIN item i ON i.nr=i.nr
Should the line above not be
... ON i.nr = o.nr ?
INNER JOIN user_cart u ON u.nr=i.nr
where (i.count !=0 or i.count!=NULL) and i.isactive=1 and i.kolWo0
order by i.count DESC
LIMIT 5
It works
2008/1/2, Edward Kay [EMAIL PROTECTED]:
Hello i use this query:
select i.item_id
from orders o
INNER JOIN item i ON i.nr=i.nr
Should the line above not be
... ON i.nr = o.nr ?
Autch. thank you! It works now.
--
Best Regards
Vlad Vorobiev
http://www.mymir.org
--
MySQL
Anoop,
It's an edge list tree, so unless you can specify max recursion depth,
you need an sproc. See listing 7c at
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html.
PB
Anoop kumar V wrote:
The resultant table should be this:
+--+---+--+
| id |
The resultant table should be this:
+--+---+--+
| id | name | mgr |
+--+---+--+
| 1001 | Denis Eaton-Hogg | NULL |
| 1002 | Bobbi Flekman | 1001 |
| 1003 | Ian Faith | 1002 |
| 1004 | David St. Hubbins | 1003 |
| 1005 |
[snip]
Is there any way to use ORDER BY in such a way as to have it ignore
words such as the, a, an, and the like?
[/snip]
I haven't tested this but you might be able to do it with a little REGEX
and a HAVING clause;
SELECT REGEX(words) AS undesirable
FROM table
HAVING stuff undesirable
--
)
-Original Message-
From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 03, 2007 7:48 PM
To: Bill Guion
Cc: mysql@lists.mysql.com
Subject: RE: Order By and Ignore Punctuation
I would suggest you order by something that includes a fulltext index on
the specific column.
Maybe check
Hi,
Bill Guion wrote:
I would like to perform a query of a personnel database with an ORDER BY
clause that ignores punctuation. For example, O'shea would sort after
Osbourne, not to the beginning of the Os.
Is this doable in the query?
If you only have a limited number of punctuation
I would suggest you order by something that includes a fulltext index on
the specific column.
Maybe check out the documentation on the MATCH()AGAINST() systax as well
as fulltext searches in general.
For example:
SELECT Col1, Col2, Score AS MATCH(TextCol) AGAINST () WHERE ... ORDER
BY Score;
Hi Edward,
Edward Kay wrote:
Hi,
I have a query that returns data from a join of two tables, person and
company. The results look like:
FIRST_NAME | LAST_NAME | COMPANY_NAME
-
NULL | NULL | Toy Co
Mark | Smith | NULL
NULL | NULL
From: Baron Schwartz
Hi Edward,
Edward Kay wrote:
Hi,
I have a query that returns data from a join of two tables, person and
company. The results look like:
FIRST_NAME | LAST_NAME | COMPANY_NAME
-
NULL | NULL | Toy Co
Mark
Hey Mike,
Sounds like you would be better of with an ENUM of integers, e.g.
ENUM(-1,1,2,3) where -1 stands for to be started, 1 for started and so on.
To answer your question:
ORDER BY `status` = 'to be started', `status` = 'started', `status` =
'finished', `status` = 'canceled'
Mike van
Thanks, that is also a solution.
Friend of mine pointed me to the following:
SELECT *, DATE_FORMAT(deadline, '%d-%m-%Y') as deadline_f,
CASE `status`
WHEN 'not yet started' then 1
WHEN 'in progress' then 4
WHEN 'finished' then 5
to the
enum list via ALTER TABLE.
-Original Message-
From: Mike van Hoof [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 21, 2007 3:19 AM
To: Christophe Gregoir
Cc: mysql
Subject: Re: ORDER BY question
Thanks, that is also a solution.
Friend of mine pointed me to the following
Add DISTINCT(primary_key) in your query?
Regards
Willy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
: 860.674.8341
-Original Message-
From: Christian Hammers [mailto:[EMAIL PROTECTED]
Sent: Friday, November 10, 2006 2:57 AM
To: Daevid Vincent
Cc: mysql@lists.mysql.com
Subject: Re: ORDER BY RAND() gives me duplicate rows sometimes
On 2006-11-09 Daevid Vincent wrote:
I am using
On 2006-11-09 Daevid Vincent wrote:
I am using this query to pull three random comments from a table:
SELECT *, DATE_FORMAT(created_on, '%b %D') as date_format FROM comments
ORDER BY RAND() LIMIT 3;
The problem is that sometimes, I get two of the same comment. How can I
refine this
I run CHECK commands against all tables nightly. Our dataset is
small, so it's quick easy; for large and/or static datasets daily
might be impractical. However, if you don't run CHECK regularly, you
don't know your data is good, and it's possible you might have
corruption for a long time
In the last episode (Oct 27), wolverine my said:
Given the commands like ANALYZE, OPTIMIZE and CHECK, what is the
preference order to execute these commands?
OPTIMIZE calculates statistics as it rebuilds the table, so there's no
need to run an ANALYZE pass after it. You shouldn't ever need to
Hi Chris,
what you can do is:
SELECT [fields]
FROM [table]
WHERE id IN (id1,id2,id3...)
ORDER BY FIELD([field],value1,value2,value3,...)
/Johan
Chris Sansom skrev:
Yes, I have looked at the docs and can't find what I'm looking for.
I'm doing a very simple query:
SELECT [fields]
FROM [table]
At 1:00 +0200 4/8/06, Johan Höök wrote:
what you can do is:
SELECT [fields]
FROM [table]
WHERE id IN (id1,id2,id3...)
ORDER BY FIELD([field],value1,value2,value3,...)
Ooh - so I can. I didn't know that wrinkle for
order by - though I did wonder if something like
that should be possible.
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
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
...just realize this might get you more confused than clearing it out
for you, but what the hey.
Thanks, your answers surely help.
But I was also interested in the question how to optimize the whole process.
Say: In a php script, I want to delete an employee-record passing his ID
to the
theo schrieb:
Hi
I' m a newbie and I'm sure this question has been answered many times, but
I can't find the appropriate thread.
Example:
I have a table of people's data including a field called say importance
like:
NAME; AGE; FUNCTION; IMPORTANCE
Peter; 24years, some job, 0
Anna; 22years,
On 6/15/06, theo [EMAIL PROTECTED] wrote:
Now, there is a new employee John with a job better than Peter's
His importance is one more than peter's, and all other move up +1;
So the list should look like this after inserting John:
Peter; 24years, some job, 0
John; 28years, somewhat better job,
PM
To: Eland, Travis M.
Cc: mysql@lists.mysql.com
Subject: Re: ORDER BY making recordset non-updatable
Maybe I'm thick
You have a view, called vwMyView.
You SELECT rows from it, and you're able to update the view?
Yet when you SELECT with an ORDER BY clause, you're not allowed to
update
itself. I don't know what to
try next.
As always, any help is greatly appreciated.
Thanks again,
Travis Eland
-Original Message-
From: sheeri kritzer [mailto:[EMAIL PROTECTED]
Sent: Fri 5/12/2006 10:01 AM
To: Eland, Travis M.
Cc: mysql@lists.mysql.com
Subject: Re: ORDER BY making
AM
To: Eland, Travis M.
Cc: mysql@lists.mysql.com
Subject: Re: ORDER BY making recordset non-updatable
That's a problem with SQL Server -- google search on your error and
you'll see that that's associated with SQL server, not MySQL.
-Sheeri
On 5/11/06, Eland, Travis M. [EMAIL PROTECTED] wrote
Eland
From: sheeri kritzer [mailto:[EMAIL PROTECTED]
Sent: Thu 5/4/2006 4:15 PM
To: Eland, Travis M.
Cc: mysql@lists.mysql.com
Subject: Re: ORDER BY making recordset non-updatable
Maybe I'm thick
You have a view, called vwMyView.
You SELECT rows from
- Original Message -
From: Mohammed Sameer [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, May 09, 2006 9:56 AM
Subject: Order by leads to an empty set.
Hi all,
I have a strange problem and I can't really understand what's going on!
mysql SELECT n.nid, n.sticky,
On Tue, May 09, 2006 at 12:13:41PM -0400, Rhino wrote:
- Original Message -
From: Mohammed Sameer [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, May 09, 2006 9:56 AM
Subject: Order by leads to an empty set.
Hi all,
I have a strange problem and I can't really
Maybe I'm thick
You have a view, called vwMyView.
You SELECT rows from it, and you're able to update the view?
Yet when you SELECT with an ORDER BY clause, you're not allowed to
update the view?
I just do not understand how a read statement affects DML. I think
you're going to have to
Your PDF is not very clear at all to me.
Is the first part trying to describe the original table and identify the
columns? Or is is pseudo code of some kind?
Is the table you present the table that the query will read or is it the
expected result?
Your example query has a WHERE clause that
Stephen A. Cochran Lists wrote:
I'm getting a strange ordering when using ORDER BY on a int column.
The rows are being returned sorted as follows:
The list is typically the way to order a string.
You are most likely to get meaningful suggestions to solve the mystery
if you include the
On Sep 27, 2005, at 2:58 AM, Jigal van Hemert wrote:
You are most likely to get meaningful suggestions to solve the
mystery if you include the table definition (output of SHOW CREATE
TABLE tablename) and the query.
mysql SHOW CREATE TABLE Player|
+
Stephen A. Cochran Lists wrote:
On Sep 27, 2005, at 2:58 AM, Jigal van Hemert wrote:
You are most likely to get meaningful suggestions to solve the
mystery if you include the table definition (output of SHOW CREATE
TABLE tablename) and the query.
mysql SHOW CREATE TABLE Player|
| Player
On Sep 27, 2005, at 3:29 AM, Jasper Bryant-Greene wrote:
mysql SHOW CREATE TABLE Player|
| Player | CREATE TABLE `Player` (
`id` int(16) NOT NULL auto_increment,
`first_name` varchar(32) NOT NULL default '',
`last_name` varchar(32) NOT NULL default '',
`year` varchar(16) NOT NULL
Stephen A. Cochran Lists wrote:
On Sep 27, 2005, at 3:29 AM, Jasper Bryant-Greene wrote:
mysql SHOW CREATE TABLE Player|
| Player | CREATE TABLE `Player` (
`id` int(16) NOT NULL auto_increment,
`first_name` varchar(32) NOT NULL default '',
`last_name` varchar(32) NOT NULL default '',
Michael Stassen wrote:
Stephen A. Cochran Lists wrote:
On Sep 27, 2005, at 3:29 AM, Jasper Bryant-Greene wrote:
mysql SHOW CREATE TABLE Player|
| Player | CREATE TABLE `Player` (
`id` int(16) NOT NULL auto_increment,
`first_name` varchar(32) NOT NULL default '',
`last_name`
On Sep 27, 2005, at 9:56 AM, Edward Vermillion wrote:
Michael Stassen wrote:
Stephen A. Cochran Lists wrote:
On Sep 27, 2005, at 3:29 AM, Jasper Bryant-Greene wrote:
mysql SHOW CREATE TABLE Player|
| Player | CREATE TABLE `Player` (
`id` int(16) NOT NULL auto_increment,
Hi,
The command in PHP is:
$query=SELECT id,first_name,last_name FROM Player ORDER BY id;
$players=mysql_query($query);
When issued from the mysql prompt, order is fine, but when
called from php I'm getting that strange order:
1, 10, 11, 12, etc...
Steve Cochran
Then
On Sep 27, 2005, at 10:28 AM, Pooly wrote:
The command in PHP is:
$query=SELECT id,first_name,last_name FROM Player ORDER BY id;
$players=mysql_query($query);
When issued from the mysql prompt, order is fine, but when
called from php I'm getting that strange order:
1, 10, 11, 12, etc...
Scott Gifford wrote:
Hello,
I'd like to sort my query results based on their distance from a given
point. The actual data I have will be in (longitude,latitude) format,
but I can convert to something else if that will work better.
For example, I may have data like this
Item
Scott Gifford [EMAIL PROTECTED] wrote on 08/24/2005 04:45:36 PM:
Hello,
I'd like to sort my query results based on their distance from a given
point. The actual data I have will be in (longitude,latitude) format,
but I can convert to something else if that will work better.
For example,
On 17/08/2005, Schimmel LCpl Robert B wrote:
If I do a select * from the table
without an order by clause, I get the results in the order which they
were entered into the table (which is how I want them).
This is not correct (e.g. on a MyISAM table in which you have done
deletes - see
Hi,
the basic thing is that you must never assume anything on what
order you're getting your rows back if you're not using an order by.
This said I guess one way for you to do this is to add a row-number
column, preferbly auto-increment, and then order by that column.
/Johan
Schimmel LCpl
Johan Höök wrote:
Hi,
the basic thing is that you must never assume anything on what
order you're getting your rows back if you're not using an order by.
This said I guess one way for you to do this is to add a row-number
column, preferbly auto-increment, and then order by that column.
/Johan
Hi
this,among other answers, can be done :
mysql select * from names;
+--+
| name |
+--+
| |
| The |
| |
| The |
| |
+--+
5 rows in set (0.02 sec)
mysql select * from names order by replace(name,'The ','');
Hi,
you didn't give an alternative, but i've forgotten just a '^' :
mysql SELECT * FROM names ORDER BY REPLACE(name,'The ','');
++
| name |
++
| |
| The |
| The |
| |
| |
| |
Mathias wrote:
you didn't give an alternative, but i've forgotten just a '^' :
mysql SELECT * FROM names ORDER BY REPLACE(name,'^The ','');
No, sorry -- that doesn't work at all; REPLACE takes a string,
not a regex. Look at your example below: 'The ' should be
after ''; ''
Right,
i have all my attention on the The Yeti order, and didn't see the rest.
This is the right structure including The in the middle :
mysql SELECT * FROM names ORDER BY case when substring(name,1,3)='The' then
REPLACE(name,'The ','')
- else name end;
++
| name
Mathias wrote:
This is the right structure including The in the middle :
mysql SELECT * FROM names ORDER BY case when substring(name,1,3)='The'
then REPLACE(name,'The ','')
else name end;
? all of which produces exactly the same result as:
SELECT * FROM names ORDER BY
Selon Hassan Schroeder [EMAIL PROTECTED]:
Mathias wrote:
This is the right structure including The in the middle :
mysql SELECT * FROM names ORDER BY case when substring(name,1,3)='The'
then REPLACE(name,'The ','')
else name end;
? all of which produces exactly the
You can use:
SELECT ..
order by case substring(Name,1,4) when 'The ' then
substring(Name,5,800) else Name end
Un saludo
Juan Pedro
Jack Lauman wrote:
I'm using a query similar to the following to get an ordered list.
SELECT ORDER BY Subscriber ASC, Name ASC;
How do I change this
You can use:
SELECT ..
order by case substring(Name,1,4) when 'The ' then
substring(Name,5,800) else Name end
Un saludo
Juan Pedro
Jack Lauman wrote:
I'm using a query similar to the following to get an ordered list.
SELECT ORDER BY Subscriber ASC, Name ASC;
How do I change
Jack Lauman wrote:
SELECT ORDER BY Subscriber ASC, Name ASC;
How do I change this so that if the 'Name' field begins with The that
the sort begins on the second word? In other words I'd like to be able
to return the word The but have it sort on whatever the second word is.
SELECT...
Is The your only problem word? What about A or An and other words that
are usually ignored when sorting things like book titles?
I'd be surprised if there was any way to ignore specific words in an ORDER
BY; I've been writing SQL for 20+ years and have never seen anything like
that.
I think what
1 - 100 of 343 matches
Mail list logo
