Hi,
Can someone perhaps assist with the below... I'm not sure at all why my
index aren't being used for the ORDER BY. Currently some 443K records in
the table, but this will grow to a good few million. I simply cannot,
afford a filesort.
mysql SELECT COUNT(*) FROM myTable
assist with the below... I'm not sure at all why my
index aren't being used for the ORDER BY. Currently some 443K records in
the table, but this will grow to a good few million. I simply cannot,
afford a filesort.
mysql SELECT COUNT(*) FROM myTable;
+--+
| COUNT
masik m STRAIGHT_JOIN egyik e ON e.id=
m.id WHERE e.duma= 'aaa' ORDER BY m.szam ASC;
++-+---++---+--+-+-+--+-+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra
Hi!
I have two tables:
CREATE TABLE `egyik` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`duma` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `e_idx` (`duma`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `egyik` VALUES (1,'aaa'),(2,'bbb');
CREATE TABLE
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
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
Hello,
I am currently upgrading from mysql 5.1.72 - mysql 5.6 and the migration and
upgrade is sound (In a QA ENV). Queries work etc. However, when I run a query
similar to SELECT `table`.* FROM `table` WHERE (some_id IN (13528, 14906,
38845)) ORDER BY `date` DESC LIMIT 1; on 5.1.72
Hi Russ,
Please share with us the exact details of the table data as well as the
o/p.
Regards,
ViXiD
Vikas Shukla
Mail Sent from my Windows Phone From: Russ Lavoie
Sent: 27-12-2013 23:32
To: mysql@lists.mysql.com
Subject: MySQL Descending ORDER issue
Hello,
I am currently upgrading from mysql
`table_foo` WHERE (credential_id IN (13528, 14906,
38845)) ORDER BY `date` DESC LIMIT 1;
Thanks!
From: myfriendvi...@gmail.com
Date: Fri, 27 Dec 2013 10:27:28 -0800
Subject: RE: MySQL Descending ORDER issue
To: russ_lav...@hotmail.com; mysql@lists.mysql.com
Hi Russ,
Please share with us
)) ORDER BY `date` DESC LIMIT 1; on 5.1.72 and on
5.6.15 I get back different data. The date for all of the data are
exactly the same to the second.
However, if I run SELECT `table`.* FROM `table` WHERE (credential_id IN
(13528, 14906, 38845)) ORDER BY `date` LIMIT 1; it comes back
Hello,
nbsp; I want to place an order in your store,and i will like to know if you
ship to Australia and my method of payment will be credit card.so please let me
know if you can assist me with the order ,And please do not forget to include
your web page in your replying back to my mail.I
- Original Message -
From: Akshay Suryavanshi akshay.suryavansh...@gmail.com
I am not sure, but if its a MyISAM table, it should be ordered by the
records insertion order, and in case of InnoDB it should be ordered
by the clustered index, not necessarily it should be a defined one
:
- Original Message -
From: Akshay Suryavanshi akshay.suryavansh...@gmail.com
I am not sure, but if its a MyISAM table, it should be ordered by the
records insertion order, and in case of InnoDB it should be ordered
by the clustered index, not necessarily it should be a defined one
- Original Message -
From: Akshay Suryavanshi akshay.suryavansh...@gmail.com
I was referring to a condition when there is no index on the tables,
not even primary keys.
If you have a lot of data in there, may I suggest you (temporarily) add a
unique index and benchmark both methods?
I am not sure, but if its a MyISAM table, it should be ordered by the
records insertion order, and in case of InnoDB it should be ordered by the
clustered index, not necessarily it should be a defined one.
On Thu, Dec 13, 2012 at 12:58 PM, jiangwen jiang jiangwen...@gmail.comwrote:
hi, all
On 10/15/2012 7:15 PM, spameden wrote:
Thanks a lot for all your comments!
I did disable Query cache before testing with
set query_cache_type=OFF
for the current session.
I will report this to the MySQL bugs site later.
First. What are all of your logging settings?
SHOW GLOBAL
,
meta_data, task_id, msgid FROM send_sms_test FORCE INDEX (priority_time)
WHERE time = UNIX_TIMESTAMP(NOW()) ORDER by priority LIMIT 0,50;
2012/10/16 Shawn Green shawn.l.gr...@oracle.com
On 10/15/2012 7:15 PM, spameden wrote:
Thanks a lot for all your comments!
I did disable Query cache before
(priority_time)
WHERE time = UNIX_TIMESTAMP(NOW()) ORDER by priority LIMIT 0,50;
2012/10/16 Shawn Green shawn.l.gr...@oracle.com
On 10/15/2012 7:15 PM, spameden wrote:
T...
Interesting thought, but I get the same result.
# Query_time: 0.001769 Lock_time: 0.001236 Rows_sent: 0 Rows_examined: 0
use kannel;
SET timestamp=1350413592;
select * from send_sms FORCE INDEX (priority_time) where time=@ut order by
priority limit 0,11;
the MySQL i'm using is 5.5.28 from
2012/10/16 12:57 -0400, Michael Dykman
your now() statement is getting executed for every row on the select. try
ptting the phrase up front
as in:
set @ut= unix_timestamp(now())
and then use that in your statement.
Quote:
Functions that return the current date or time each are evaluated only
That's exactly what I thought when reading Michael's email, but tried
anyways, thanks for clarification :)
2012/10/16 h...@tbbs.net
2012/10/16 12:57 -0400, Michael Dykman
your now() statement is getting executed for every row on the select. try
ptting the phrase up front
as in:
set @ut=
))
If I check with EXPLAIN MySQL says it would use the index:
mysql *desc select * from send_sms_test where time=UNIX_TIMESTAMP(NOW())
order by priority limit 0,11;*
++-+---+---+---+---+-+--+--+-+
| id | select_type
INDEX (time_priority) where
time=UNIX_TIMESTAMP(NOW()) order by priority limit 0,13;*
++-+---+---+---+---+-+--+---+-+
| id | select_type | table | type | possible_keys | key
| key_len | ref
over-sized.
-Original Message-
From: spameden [mailto:spame...@gmail.com]
Sent: Monday, October 15, 2012 1:42 PM
To: mysql@lists.mysql.com
Subject: mysql logs query with indexes used to the slow-log and not
logging if there is index in reverse order
Hi, list.
Sorry
send_sms_test FORCE INDEX (time_priority) where
time=UNIX_TIMESTAMP(NOW()) order by priority;
++-+---+---+---+---+-+--+---+-+
| id | select_type | table | type | possible_keys | key
| key_len | ref
:
mysql desc select * from send_sms_test FORCE INDEX (time_priority) where
time=UNIX_TIMESTAMP(NOW()) order by priority;
++-+---+---+---+---+-+--+---+-+
| id | select_type | table | type
;
filesort
INDEX(priority, time) -- faster; smaller; seems to use both keys of the
index (key_len=12); avoids filesort (because INDEX(priority, ...) agrees with
ORDER BY priority).
The Optimizer has (at some level) two choices:
* Start with the WHERE
* Start with the ORDER BY
Since
[mailto:spame...@gmail.com]
Sent: Monday, October 15, 2012 3:29 PM
To: Rick James
Cc: mysql@lists.mysql.com
Subject: Re: mysql logs query with indexes used to the slow-log and not logging
if there is index in reverse order
Sorry, forgot to say:
mysql show variables like 'long_query_time
...@gmail.com]
*Sent:* Monday, October 15, 2012 3:29 PM
*To:* Rick James
*Cc:* mysql@lists.mysql.com
*Subject:* Re: mysql logs query with indexes used to the slow-log and not
logging if there is index in reverse order
** **
Sorry, forgot to say:
mysql show variables like 'long_query_time
2012/06/20 14:32 -0700, Rick James
(
SELECT ruid1, ruid2, overlap FROM l4_link WHERE ruid1=xxx
UNION
SELECT ruid1, ruid2, overlap FROM l4_link WHERE ruid2=xxx
) ORDER BY overlap DESC;
Make it UNION ALL or UNION DISTINCT depending on whether xxx can be in both
fields of one row. UNION
The parens are for making sure the parsing works correctly. Probably either
one works fine. Suggest you do
EXPLAIN EXTENDED ...;
SHOW WARNINGS;
I suspect that the output from each will be identical, and have more parens.
The main need for parens is to avoid associating the ORDER
I have mysql 5.1.62-0ubuntu0.11.10.1 installed, but the sort order for
utf8-unicode doesn't seem to be right. It is sorting some text like this
(order by title):
!
! *`-=[];',./~@#$%^()_+{}|:?\
!
![
!@
!a
!A
!t
!test
'Coal age'
[
\--\
100 years
when it should be (shouldn't
Hi Wes, have you double checked you character set and collation ??
http://dev.mysql.com/doc/refman//5.5/en/charset-charsets.html
The collation is which determines the sorting order.
Carlos Proal
On Wed, May 30, 2012 at 4:02 PM, Wes James compte...@gmail.com wrote:
I have mysql 5.1.62
the sorting order.
Carlos Proal
On Wed, May 30, 2012 at 4:02 PM, Wes James compte...@gmail.com wrote:
I have mysql 5.1.62-0ubuntu0.11.10.1 installed, but the sort order for
utf8-unicode doesn't seem to be right. It is sorting some text like this
(order by title
In phpMyAdmin it says the database collation is utf8_unicode_ci and each of
the columns is utf8_unicode_ci except the ID primary key column.
If you create a simple table with ID and TITLE and put the data below (each
row in a record) and run 'select * from table order by title' what do you
get
: Wednesday, May 30, 2012 2:03 PM
To: mysql general discussion
Subject: sort order
I have mysql 5.1.62-0ubuntu0.11.10.1 installed, but the sort order for
utf8-unicode doesn't seem to be right. It is sorting some text like
this (order by title
= 5653
ORDER BY miles ASC, cancels/(prods_done/cancels) ASC, prods_done DESC;
The results look something like this:
Jim Barnes Chicago, IL 773-555- 2 1 11.5
Kelley Smith Cicero, IL 708-444-2121 3 0 21.6
Kim Ayers Plainfield, IL 630-888-9898 22 1 25.1
I am trying to find a way to give
SELECT name, city, state, phone, prods_done, cancels, miles FROM
(SELECT name, city, state, phone, prods_done, cancels, miles, ((prod_done -
cancels) * 100 / prod_done) reliability
FROM volunteer_search WHERE project_id = 5653) A
ORDER BY reliability DESC, miles ASC
Give it a try !!!
Rolando
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,1)
will give me results in this order:
+--+-+
| id
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
-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
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 function output
with am and pm. I guess I should have named things differently but I
would rather
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
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 rows in table
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
.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com
-Original Message-
From: Kristian Davies [mailto:kristian.dav...@gmail.com]
Sent: Wednesday, August 04, 2010 5:03 AM
To: mysql@lists.mysql.com
Subject: ORDER BY LIMIT issue
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
-31'
and sales_journal.date '2010-01-01'
group by sales_journal_entries.accountID
order by Balance asc;
and I'd like the output to be sorted by the Balance according to the
numberic value, but it is sorting by the string result. I tried
abs(Balance) but I get the following error:
1247 Reference
sales_journal.date '2008-12-31'
and sales_journal.date '2010-01-01'
group by sales_journal_entries.accountID
order by Balance asc;
and I'd like the output to be sorted by the Balance according to the
numberic value, but it is sorting by the string result. I tried
abs(Balance) but I get the following
chart_of_accounts
on chart_of_accounts.accountID=sales_journal_entries.accountID
where sales_journal.date '2008-12-31'
and sales_journal.date '2010-01-01'
group by sales_journal_entries.accountID
order by Balance asc;
and I'd like the output to be sorted by the Balance according
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
)) |
+---+
| 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.
Regards,
Gavin Towey
-Original Message-
From: DaWiz [mailto:da...@dawiz.net]
Sent
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
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
Does the order of tuples in a bulk insert impact later query
performance? E.g. will sorting the rows before a bulk insert cause
queries to perform better for indexed or non-indexed fields?
Thanks,
Anthony
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
On 2/7/10 7:28 AM, Anthony Urso antho...@cs.ucla.edu wrote:
Does the order of tuples in a bulk insert impact later query
performance? E.g. will sorting the rows before a bulk insert cause
queries to perform better for indexed or non-indexed fields?
when i load a large body of data (using
Hi
how to use order by with with rollup, if it is not possible is there any
alternative,
in rollup how to name the null. is there chance to do so. please help me
with this..
.role_id=2556 order by item0_.id desc;
mysql version: 5.1.34
have you guys met this problem?
thanks,
-Oscar
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
I'm not sure what to search on to see if someone has reported this as
a bug or if I'm doing something wrong...
Generic code to draw a SELECT element on the screen sometimes it ends
up like such...
SELECT DISTINCT name AS myvalue,name AS mydisp FROM names WHERE
name!= ORDER BY myvalue
On 4.1.22
From: Matt Neimeyer m...@neimeyer.org
Generic code to draw a SELECT element on the screen
sometimes it ends
up like such...
SELECT DISTINCT name AS myvalue,name AS mydisp FROM names
WHERE
name!= ORDER BY myvalue
On 4.1.22 this returns
A A
B B
C C
D D
On 5.0.22 this returns
From: Glyn Astill glynast...@yahoo.co.uk
Doesn't look crazy to me, and it works in 5.0.32
http://www.privatepaste.com/50RvhihKKm
Perhaps time to patch that server ...
I've guessed at the table def there, obviously your def may be different and
that would surely affect the palanners
Given table: CREATE TABLE testtab (d_col CHAR(4));
Question 1: It appears that there is no harm in just appending
directives onto the alter table command even if the order doesn't make
sense. It appears the parser figures it out... For example...
ALTER TABLE testtab ADD COLUMN c_col char(4
-
From: Matt Neimeyer [mailto:m...@neimeyer.org]
Sent: Friday, September 04, 2009 3:53 PM
To: mysql@lists.mysql.com
Subject: ALTER TABLE order / optimization
Given table: CREATE TABLE testtab (d_col CHAR(4));
Question 1: It appears that there is no harm in just appending
directives onto the alter
|
+---+---+-+
which not groups correctly. Seems it's a hard query.
- Original Message -
From: Darryle Steplight dstepli...@gmail.com
To: Elim PDT e...@pdtnetworks.net
Cc: mysql@lists.mysql.com
Sent: Wednesday, July 15, 2009 11:50 PM
Subject: Re: Hard? query to with group order by group
value.
SELECT tablename.*,
IF(tablename.head_id=NULL,
CONCAT(tablename.name, tablename.member_id),
CONCAT(heads.name, tablename.head_id)
) AS SortValue
FROM tablename LEFT JOIN tablename AS heads ON tablename.head_id=heads.member_id
ORDER BY SortValue
Brent Baisley
--
MySQL General Mailing
|
That is
(1) A head-row follewed by the group members with that head
(2)head rows are ordered alphabetically by name.
What the query looks like?
Thanks
I hope this is not a school assignment.
What I came up with was to create a new order column that I populated
with the name of the HEAD
My table group_member looks like this:
+---+---+-+
| member_id | name | head_id |
+---+---+-+
| 1 | Elim |NULL |
| 2 | Ann | 1 |
| 3 | David |NULL |
| 4 | John | 3 |
| 5 | Jane | 3 |
I see such nice formated text output serving to illustrate people's
tables and I think it must be due to some code which is spitting
that out, rather than people typing so painstakingly. What is that
function/MySQL/code?
It's the default output format of the mysql command line client,
On 16 Jul 2009, at 15:02, Govinda wrote:
I see such nice formated text output serving to illustrate people's
tables and I think it must be due to some code which is spitting
that out, rather than people typing so painstakingly. What is that
function/MySQL/code?
It's the default output
On 16 Jul 2009, at 15:26, Govinda wrote:
Meaning that on a shared hosting situation, without ssh, then I
cannot do that, right?
Not necessarily - you can run the client locally and connect to the
remote DB. It depends if your host allows remote access to mysql (they
might do on
My table group_member looks like this:
+---+---+-+
| member_id | name | head_id |
+---+---+-+
| 1 | Elim |NULL |
| 2 | Ann | 1 |
| 3 | David |NULL |
| 4 | John | 3 |
| 5 | Jane | 3 |
Hi Elim,
I didn't test it out but it sounds like you want to do this
SELECT * FROM group_members GROUP BY head_id, member_id ORDER BY name
ASC .
On Thu, Jul 16, 2009 at 1:20 AM, Elim PDTe...@pdtnetworks.net wrote:
My table group_member looks like
PJ wrote:
Let me put it this way, I am not having the problem. The problem seems
to be withthe way that character encoding is set up on the internet -
as confused and inconsistent as most everything else.
You can put whatever charset you want in the header, in the collations
in your
I agree with Per, I use utf8 and it works fine for me, even with Chinese
characters
On Fri, Jun 12, 2009 at 8:40 AM, Per Jessen p...@computer.org wrote:
PJ wrote:
Let me put it this way, I am not having the problem. The problem seems
to be withthe way that character encoding is set up on
Is there a way to order lists while ignoring the accents?
So far, I have found nothing simple; and I need to keep the accents for
output.
The language is French (and québécois) :-)
TIA
--
Hervé Kempf: Pour sauver la planète, sortez du capitalisme
Hi,
I'm not having any problem on my local computer
mysql select text,text2 from table1 order by text2 desc;
+--+---+
| text | text2 |
+--+---+
| a| 1 |
| �| 0 |
+--+---+
mysqlselect text,text2 from table1 order by text2 desc;
+--+---+
| text
Isart Montane wrote:
Hi,
I'm not having any problem on my local computer
mysql select text,text2 from table1 order by text2 desc;
+--+---+
| text | text2 |
+--+---+
| a   |    1 |
| �   |    0 |
+--+---+
mysqlselect text,text2 from table1
Craig Dunn wrote:
Hi,
I'm trying to migrate an application from 4.1 to 5.1, theres a bunch of
queries that seem to be failing and it looks like the order of INNER
JOIN's... for example...
SELECTetc
INNER JOIN tablex AS x ON y.foo = a.bar
INNER JOIN tablea AS a ON y.foo = b.bar
Hi,
I'm trying to migrate an application from 4.1 to 5.1, theres a bunch of
queries that seem to be failing and it looks like the order of INNER
JOIN's... for example...
SELECTetc
INNER JOIN tablex AS x ON y.foo = a.bar
INNER JOIN tablea AS a ON y.foo = b.bar
... works in 4.1
).
Thanks,
Nishi
CREATE TABLE t ( id TINYINT, title VARCHAR(10) );
INSERT INTO t (id, title) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc'), (4,
'ddd'), (5, 'eee');
(a) DO @sn := 0; SELECT @sn:[EMAIL PROTECTED] sn, id, title FROM t GROUP BY id
ORDER BY id desc;
| sn | id | title
ORDER BY id desc;
| sn | id | title |
+--+--+---+
|1 |5 | eee |
|2 |4 | ddd |
|3 |3 | ccc |
|4 |2 | bbb |
|5 |1 | aaa |
(b) [MySQL 5.0.51a]: DO @sn := 0; SELECT @sn:[EMAIL PROTECTED] sn, id, title
FROM t
HAVING sn=2 ORDER BY id desc
Dear All,
I'm trying to create a regular expression query to match phone numbers
in a database field.
My issue is this , the numbers have no set standard for input in the db.
So the number in the db could be in multiple formats.
EX:
333.333.
(333)333-
333-333-
33
So I am
[EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Monday, December 1, 2008 6:42:19 PM
Subject: RE: regular expressions matching only numeric characters in order
Hi
I am a bit of novice at Regexp, but I believe this will work for you
(\d+\d+\d+).*(\d+\d+\d+).*(\d+\d+\d+\d+)
Robert M
CREATE TABLE t ( id TINYINT, title VARCHAR(10) );
INSERT INTO t (id, title) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc'), (4,
'ddd'), (5, 'eee');
(a) DO @sn := 0;
SELECT @sn:[EMAIL PROTECTED] sn, id, title FROM t GROUP BY id ORDER BY id desc;
| sn | id | title |
+--+--+---+
|1
Hi Nishi,
There was a bug in that version that affected the sort order when
combined with a group by statement
(http://bugs.mysql.com/bug.php?id=32202.) There are a couple of
workarounds but they are bad (e.g. removing primary key!)
I would strongly suggest you upgrade to 5.0.67
, 'bbb'), (3, 'ccc'),
(4, 'ddd'), (5, 'eee');
(a) DO @sn := 0;
SELECT @sn:[EMAIL PROTECTED] sn, id, title FROM t GROUP BY id ORDER BY id desc;
| sn | id | title |
+--+--+---+
|1 |5 | eee |
|2 |4 | ddd |
|3 |3 | ccc |
|4 |2 | bbb |
|5
, 'bbb'), (3, 'ccc'), (4,
'ddd'), (5, 'eee');
(a) DO @sn := 0; SELECT @sn:[EMAIL PROTECTED] sn, id, title FROM t GROUP BY id
ORDER BY id desc;
| sn | id | title |
+--+--+---+
|1 |5 | eee |
|2 |4 | ddd |
|3 |3 | ccc |
|4 |2 | bbb |
|5
Error 1067 The preocess terminated unexpectedly
According to the manual MySQL server reads option files in the order
Default options are read from the following files in the given order:
C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf C:\Program
Files\MySQL\MySQL
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
Hi
I've the following basic query like
SELECT ProductName FROM Products
WHERE ProductScore 100
ORDER BY ProductScore
However, how can I order by ProductScore, but ensure the product with ID 125
is at the top ? Is this possible.
Thanks
Neil
to list this data in the order of the the product with the highest
quantity, followed by ProductScore. Am I able to calculate a quantity
percentage, based on the number of records for say Red Light.
Thanks,
Neil
On Fri, Oct 24, 2008 at 1:32 PM, Tompkins Neil [EMAIL PROTECTED]
wrote:
Thanks
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
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
160 Green Light
0.115
Is this possible ?
On Fri, Oct 24, 2008 at 2:28 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote:
SELECT ProductID,
ProductName,
AVG(ProductScore * Quantity) AS a
FROM Products
GROUP BY ProductID
ORDER BY a DESC
2008/10/24
, Oct 24, 2008 at 2:28 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote:
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
1 - 100 of 1475 matches
Mail list logo
