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
T
> -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
> wrote:
> > Hi,
> >
&
On Tue, Nov 9, 2010 at 10:09 AM, Mark Goodge 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 (10,3,8,5,
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'
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 h
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 form
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.
Fa
> 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. I
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
>
> 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,
concat('$',format(coalesce(sum(sales_journ
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 Acco
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
Debi
---+
> | 1.00 |
> +---+
> 1 row in set (0.00 sec)
>
>
> Which in that case, it's better to just select balance without the dollar
> sign and order on that column.
er 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 as decimal(8,2)) asc;
Cast will work in the order b
Try
order by CAST(Balance as decimal(8,2)) asc;
Cast will work in the order by.
Glenn Vaughn
- Original Message -
From: "Keith Clark"
To:
Sent: Tuesday, April 27, 2010 3:52 PM
Subject: order by numeric value
I have the following statement:
select chart_of_accounts.accountname
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,
> > concat('$',format(coalesce(sum(sales
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,
concat('$',format(coalesce(sum(sales_jou
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, Ol
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
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
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
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
%2008-11-
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
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 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
- 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 C
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="
=
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
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> > Sebast
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 this
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,
COUNT(ProductsPu
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
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:> >
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 Pro
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.or
>
> 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.kolWo>0
> order by i.count DESC
> LIMIT 5
>
[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
-+
| Olathe |
| O'Malley |
| Osbourn |
| O'shea |
| Ottawa |
+--+
5 rows in set (0.00 sec)
-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 Ig
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;
Ho
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 charac
> 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 |
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
dd a value 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
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
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 Hoo
.674.8796 / FAX: 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
>
>
>
>
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]
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 t
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.
Th
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]
ri 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 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, n
ual Basic, MyODBC or MySQL 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
Sub
: 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 it, and you're able to update the view?
ut is greatly appreciated.
Thanks,
Travis 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
On Tue, May 09, 2006 at 12:13:41PM -0400, Rhino wrote:
>
> - Original Message -
> From: "Mohammed Sameer" <[EMAIL PROTECTED]>
> To:
> 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 underst
- Original Message -
From: "Mohammed Sameer" <[EMAIL PROTECTED]>
To:
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, n.created FROM node n WH
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 pos
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
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...
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
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,
`first_name
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` varchar(32
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 '',
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 defaul
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 ) and the query.
mysql> SHOW CREATE TABLE Player|
| Player | CREATE
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 ) and the query.
mysql> SHOW CREATE TABLE Player|
+
+---
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 table
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
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 exam
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,
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 Rober
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
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 T
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:
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 ''; '' shou
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 |
| |
| |
| |
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
My thanks to all that responded. I used Mathias's suggestion to solve
the problem. You can see the results here.
http://www.tasteofwhatcom.com/restaurants-tow/filter.jsp?field=city&value=Blaine
Thanks again for your help.
Jack
Mathias wrote:
Selon Jack Lauman <[EMAIL PROTECTED]>:
I'm u
Selon Jack Lauman <[EMAIL PROTECTED]>:
> 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 so that if the 'Name' field begins with "The " that
> the sort begins on the second word? In other words I'd like
ose of DB2, which is
usually true. Not this time though!
Rhino
- Original Message -
From: "Hassan Schroeder" <[EMAIL PROTECTED]>
To: "Jack Lauman" <[EMAIL PROTECTED]>;
Sent: Sunday, June 26, 2005 2:44 PM
Subject: Re: ORDER by Question
> Jack Lauman wr
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
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.
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 this
> Or put the members alphabetically in the enum definition in the first
> place ...
Better yet - drop the ENUM al together :-)
Use a lookup table.
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upsc
Or put the members alphabetically in the enum definition in the first
place ...
--
felix
On 09/06/2005, Eric Bergen wrote:
> It's not a bug at all. You just hit one of the features of enum :)
>
> If you want to order alphabetically as you describe cast the enum
> name to a string like this se
I would say this is not a bug. You declared an enum for the column. So
therefore it sorts in enum order. Makes perfect sense. To me MySql is
working correctly. If it did not sort an enum in the order declared for
the enum then i would be annoyed. Enums are not strings.
Declare the column a
It's not a bug at all. You just hit one of the features of enum :)
If you want to order alphabetically as you describe cast the enum name
to a string like this
select col from t order by concat(my_enum);
-Eric
Daevid Vincent wrote:
Please tell me there is a way to fix this "bug" in mysql V
Schalk Neethling wrote:
Greetings!
This might be a stupid question but here goes:
I have a table that contains a column entitled current_pos. I want to
search this table and then order the results by current_pos. Now I am
running the following SQL query on the table:
SELECT * FROM ab_leader_boar
[snip]
This might be a stupid question but here goes:
I have a table that contains a column entitled current_pos. I want to
search this table and then order the results by current_pos. Now I am
running the following SQL query on the table:
SELECT * FROM ab_leader_board WHERE sex = 'F' and cup =
On Fri, 29 Apr 2005 08:20:58 +0200, Stano Paska <[EMAIL PROTECTED]>
wrote:
Hi,
in my table I have one varchar(20) column where I store version number.
Version looks like:
1.1.2
1.2.1
1.10.3
It is possible order this column in natural order (1.2 before 1.10)?
Stano.
Hello Stano,
What about divide
Hi,
select a from versions order by substring_index(a,'.',-2);
Best Regards
Mathias FATENE
Hope that helps
*This not an official mysql support answer
-Original Message-
From: Stano Paska [mailto:[EMAIL PROTECTED]
Sent: vendredi 29 avril 2005 08:21
To: mysql@list
Hi Stano,
there was a response by Michael Stassen on the list about a year ago on
this problem, which I hope he doesn't mind if I repeat below.
/Johan
Version numbers?
CREATE TABLE ss (version VARCHAR(13));
INSERT INTO ss VALUES ('1'),('1.1'),('1.2.1.2'),('1.10.1'),('1.2'),
- Original Message -
From: "Jalil Feghhi" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, December 19, 2004 3:37 PM
Subject: Order By When Grouping
> Let's say I have a table w/ three rows: ID, Time, and Name. when I do:
>
First of all, I think you mean to say that your table
Mike,
Try select * from foo order by x+0, x;
x+0 converts x to an integer by taking the digits from the beginning of the
string.
== original message follows ==
Date: Sat, 11 Dec 2004 15:36:34 -0600
From: Mike Blezien <[EMAIL PROTECTED]>
To: MySQL List <[EMAIL PROTECTED]>
Subject
In article <[EMAIL PROTECTED]>,
dan orlic <[EMAIL PROTECTED]> writes:
> i have an question about ordering a set of records...
> ab c d
> -
> 1Tax 120001.33
> 1Tax 115002.5
You can try to use two "subqueries" and an union ala
SELECT * FROM tab where c>0 ORDER by C UNION ALL SELECT * FROM tab where
c=0;
Hagen
dan orlic wrote:
i have an question about ordering a set of records...
ab c d
-
1
Hi.
May be it will be helpful:
http://dev.mysql.com/doc/mysql/en/Charset.html
For commercial support go to:
https://order.mysql.com/?ref=ensita
Alaios <[EMAIL PROTECTED]> wrote:
> Hi.. Listen to a peculiar problem.
> We need to order by a column which includes Greek
> language. T
write:
order by date desc
DeRyl
- Original Message -
From: "Jerry Swanson" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, November 05, 2004 8:00 PM
Subject: ORDER by date: reverse order
I want to sort by date but the last date appears first. How to write such query?
TH
--
1 - 100 of 297 matches
Mail list logo
