for num_of_day); expecting the
output: certain_day, pigId;from certain_day, pigId'weight increasing
continuously for num_of_day. How to select the records in one sql
statement?
thanks
I've thought about this a bit (since your question appeared on the list)
and I break down the tasks you
certain_day, pigId'weight increasing
continuously for num_of_day. How to select the records in one sql
statement?
thanks
Dear friends,
1. https://bugs.mysql.com/bug.php?id=78934
2.
https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id
The document (2) says that LAST_INSERT_ID() will not be changed if no rows
are inserted successfully. But (1) says that it's undefined when no
Select class_name,
GROUP_CONCAT(DISTINCT cl_date
ORDER BY cl_date DESC SEPARATOR ', ')
(select min(cl_date) from CLASS_DATES where item_id = c.item_id and
cl_date > Now())
From CLASSES c
Join CLASS_DATES cd on (c.item_id = cd.item_id)
Gr
s the
KEY field.
I am compiling a SELECT query to search the dates of the classes
(tl_items_classes rows), but I need to these two things:
1) Only show the EARLIEST “cl_date" in my returned list (i.e... if a class has
dates for Jan 2,3,4,5,6 2016, only show Jan 2’s row)
2) In that dis
I need help creating a select that returns 4 records that have
contiguous addresses that start on a bit boundary.
If 4 do not exist, I need a return of zero records.
I would like to do this in one statement and I do not have ownership of
this mysql server, so fancy views, temporary tables
I should have said consecutive addresses, rather than contiguous.
I care about a set of consecutive addresses, and there is no guarantee
of record order.
On 12/17/2015 07:35 AM, John Stile wrote:
> I need help creating a select that returns 4 records that have
> contiguous addresses that
I have a solution.
SELECT start_bit_boundary FROM (
SELECT
min(address) as start_bit_boundary,
status, count(*) as CT
FROM MAC_addresses
WHERE status = 0
GROUP BY address >> 2
) AS _INNER
WHERE
_INNER.CT = 4
ORDER BY start_bit_boundary
LIMIT 0,1;
It returns the firs
NOT NULL AUTO_INCREMENT which is also a key
KEY auxiliary(seq). This is useful a posteriori to locate particular
records.
What is the command to select all the records where an item_number has
the data 1 but not the data 2?
1) by select you mean display at the terminal using the mysql line mode
Right,
Take a look at this one then:
insert into test(item_number,data_value)
values(1,1),(1,2),(1,3)
,(2,1),(2,3)
,(3,1),(3,2),(3,3)
,(4,1),(4,3);
SELECT * FROM test
WHERE item_number in (SELECT item_number FROM test where data_value=1)
AND item_number not in (SELECT item_number FROM test
Simply translated:
select * from table t1
where t1.data_value=1
AND not exists(select * from table t2
where t2.data_value=2
and t2.item_number = t1.item_number)
Axel Diehl
__
GIP Exyr GmbH
Hechtsheimer Str. 35-37 | 55131 Mainz
Tel: +49 (0
Axel,
Simply translated:
select * from table t1
where t1.data_value=1
AND not exists(select * from table t2
where t2.data_value=2
and t2.item_number = t1.item_number)
Yes, but with t1 and t2 the same table.
best regards,
Olivier
Axel Diehl
Thank you,
SELECT * FROM test
WHERE item_number in (SELECT item_number FROM test where data_value=1)
AND item_number not in (SELECT item_number FROM test where data_value = 2);
That did it.
Olivier
On Wed, April 29, 2015 07:20, Olivier Nicole wrote:
SELECT * FROM table WHERE item_number
is also a key
KEY auxiliary(seq). This is useful a posteriori to locate particular
records.
I do, but that was irrelevant to my question, as it is only counting the
records, it carries no information.
What is the command to select all the records where an item_number has
the data 1 but not the data
On 2015-04-29 12:20 AM, Olivier Nicole wrote:
SELECT * FROM table WHERE item_number=1;
Sorry if my question was not clear: what I am looking for is:
SELECT * FROM table WHERE data_value=1 AND there is not any reccord with
the same item_number and data_value=2
Assuming a table named t
and a different data_value.
What is the command to select all the records where an item_number has
the data 1 but not the data 2?
Thanks in advance,
Olivier
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
SELECT * FROM table WHERE item_number=1;
On Wed, April 29, 2015 06:30, Olivier Nicole wrote:
Hi,
I am sure that it is feasible with MySQl, and I am sure that's a newbie
question, but my SQL skills are limited...
I have a table where each record is made of one item_number and one
data_value
SELECT * FROM table WHERE item_number=1;
Sorry if my question was not clear: what I am looking for is:
SELECT * FROM table WHERE data_value=1 AND there is not any reccord with
the same item_number and data_value=2
Olivier
On Wed, April 29, 2015 06:30, Olivier Nicole wrote:
Hi,
I am sure
On Feb 12, 2014, at 6:30 PM, Larry Martell wrote:
Try adding a having clause, e.g.:
SELECT `ip`,`page`,`url`,`time_stamp`
FROM `ip_addresses`
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE ''
HAVING COUNT(ip
Hi Jennifer,
please try filtering with a subquery that locates ip addresses with more than 1
attempt:
SELECT ip, page, url, time_stamp
FROM ip_adresses
WHERE existing where clause AND
ip IN (SELECT ip
FROM ip_addresses
WHERE existing where clause
could do it, but there's an error
somewhere in the SQL. What am I doing wrong?
CREATE TEMPORARY TABLE temp_ip AS
(SELECT `ip`
FROM `ip_addresses`
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE ''
GROUP
Hi Jennifer,
great that it worked.
Try replacing the line
`ip` IN (temp_ip)
with
`ip` IN (SELECT ip FROM temp_ip)
Each subquery needs to be a complete SELECT query.
Thanks,
Roy
On 17.02.14 21:11, Jennifer wrote:
On Feb 17, 2014, at 10:17 AM, Roy Lyseng wrote:
please try filtering
Hi Jennifer,
On 2/17/2014 3:11 PM, Jennifer wrote:
CREATE TEMPORARY TABLE temp_ip AS
(SELECT `ip`
FROM `ip_addresses`
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE ''
GROUP BY `ip`
HAVING COUNT
On Feb 17, 2014, at 12:36 PM, Roy Lyseng wrote:
Try... `ip` IN (SELECT ip FROM temp_ip)
Wow! Only 1 second to return the results now!! That's 15x faster!!!
Each subquery needs to be a complete SELECT query.
That's good to know. I figured that since temp_ip referenced
.
How can I add a condition to only show hits by someone who's hit the
site 2 or more times with the same IP? I tried GROUP BY but that didn't return
all the hits - one one per IP.
SELECT `ip`,`page`,`url`,`time_stamp`
FROM `ip_addresses`
WHERE (`time_stamp` BETWEEN date_add( CURDATE
to report on multiple hits
from the same IP address - not just a single hit by someone.
How can I add a condition to only show hits by someone who's hit the
site 2 or more times with the same IP? I tried GROUP BY but that didn't
return all the hits - one one per IP.
SELECT `ip`,`page
2013/06/13 23:08 +, Rick James
FIND_IN_SET might work the cleanest...
WHERE FIND_IN_SET('action', genres) OR/AND [NOT] ...
And have genres look like 'action,drama,foobar', that is comma-separators, and
no need for leading/trailing comma.
That would also work for genres = '1,3,10,19,38' and
basically...
SELECT * FROM scene_all_genres WHERE scene_id = 17;
scene_id genres
17 1|3|10|19|38|53|58|59|
SELECT * FROM scene_all_genres WHERE scene_id = 11;
scene_id genres
---
11 1|10
basically...
SELECT * FROM scene_all_genres WHERE scene_id = 17;
scene_id genres
17 1|3|10|19|38|53|58|59|
SELECT * FROM scene_all_genres WHERE scene_id = 11;
scene_id genres
---
11 1|10
, 2013 4:17 PM
To: mysql@lists.mysql.com
Cc: 'shawn green'
Subject: RE: How do I select all rows of table that have some rows in
another table (AND, not OR)
-Original Message-
From: shawn green [mailto:shawn.l.gr...@oracle.com]
Sent: Tuesday, June 11, 2013 2:16 PM
To: mysql
Hello Daevid,
On 6/11/2013 7:17 PM, Daevid Vincent wrote:
-Original Message-
From: shawn green [mailto:shawn.l.gr...@oracle.com]
Sent: Tuesday, June 11, 2013 2:16 PM
To: mysql@lists.mysql.com
Subject: Re: How do I select all rows of table that have some rows in
another table
: 'shawn green'
Subject: RE: How do I select all rows of table that have some rows in
another table (AND, not OR)
Thinking out of the box... (And posting my reply at the 'wrong' end of the
email.)...
Are there fewer than 64 genres? Use a SET or BIGINT UNSIGNED
Oh! I must have misread. I didn't see how you had a solution for 64 bits.
I may have to experiment with that!
-Original Message-
From: Daevid Vincent [mailto:dae...@daevid.com]
Sent: Wednesday, June 12, 2013 11:26 AM
To: mysql@lists.mysql.com
Subject: RE: How do I select all rows
AM
To: mysql@lists.mysql.com
Subject: RE: How do I select all rows of table that have some rows in
another table (AND, not OR)
This idea is so fancy pants and clever I *wish* it could have worked for
me.
I checked and we actually have 65 genres currently (with more to come I'm
sure) LOL
I select all rows of table that have some rows in
another table (AND, not OR)
I'm the ORIGINAL Rick James, B (And, I'm still alive.) LOL
If you are using PHP, you might want to stop at 31 bits per INT/SET. PHP seems
not to yet be in the 64-bit world.
-Original Message-
From
I am trying to implement a filter so that a user could select various genres
they want in or out. Perhaps they like 'action' and 'car chases' but
don't like 'foreign' and 'drifting' (or whatever...)
So I want something sort of like this, however IN() is using an OR
comparison when I need
Hello Daevid,
On 6/11/2013 3:59 PM, Daevid Vincent wrote:
I am trying to implement a filter so that a user could select various genres
they want in or out. Perhaps they like 'action' and 'car chases' but
don't like 'foreign' and 'drifting' (or whatever...)
So I want something sort of like
-Original Message-
From: shawn green [mailto:shawn.l.gr...@oracle.com]
Sent: Tuesday, June 11, 2013 2:16 PM
To: mysql@lists.mysql.com
Subject: Re: How do I select all rows of table that have some rows in
another table (AND, not OR)
Hello Daevid,
On 6/11/2013 3:59 PM, Daevid
You can do:
SELECT last_name, first_name, phone, if(pub_email=Y,email,) as email
FROM `mydatabasetable` WHERE `current_member` = Y AND `pub_name` = Y
ORDER BY last_name ASC
Gracias, Carlos. This worked fine!
---Fritz
--
MySQL General Mailing List
For list archives: http
De-lurking here.
I am trying to figure out how to return results from a query. What I need to do
is to return 4 columns from a database. This is easy:
SELECT last_name, first_name, phone, email FROM `mydatabasetable` WHERE
`current_member` = Y AND `pub_name` = Y ORDER BY last_name ASC
Try using a CASE construct in the select. Should work for this.
A
On Tue, Feb 5, 2013 at 3:25 PM, Stefan Kuhn stef...@web.de wrote:
You cannot do this. A sql result alwas has the same number of columns in
each row. You could have null or in the column, though. This could be
done via
. Februar 2013 um 15:49 Uhr
Von: cl c...@nimbleeye.com
An: mysql@lists.mysql.com
Betreff: SELECT subquery problem
De-lurking here.
I am trying to figure out how to return results from a query. What I need to do
is to return 4 columns from a database. This is easy:
SELECT last_name, first_name, phone
2013/02/02 12:58 -0600, Peter Brawley
On 2013-02-01 10:18 PM, h...@tbbs.net wrote:
2013/01/31 22:24 -0600, Peter Brawley
Is this what you mean?
Select,
pricelist
If( !IsNull(specialprice) And specialprice unitprice And CurDate() Between
startingDate And endingDate,
specialprice,
unitprice
2013/01/31 22:24 -0600, Peter Brawley
Is this what you mean?
Select,
pricelist
If( !IsNull(specialprice) And specialprice unitprice And CurDate() Between
startingDate And endingDate,
specialprice,
unitprice
) as used_price
From catalog
Where itemid='WB314';
PB
Maybe this is gilding
On 2013-02-01 10:18 PM, h...@tbbs.net wrote:
2013/01/31 22:24 -0600, Peter Brawley
Is this what you mean?
Select,
pricelist
If( !IsNull(specialprice) And specialprice unitprice And CurDate() Between
startingDate And endingDate,
specialprice,
unitprice
) as used_price
From catalog
Where
Hello,
I'm trying to write a select query that grabs two prices from my db and
displays them on a web page. I want it to grab the `listprice`, and either the
`unitprice` or the `specialprice` depending on the following criteria:
if the `specialprice` is not empty,
AND it's less than
On 2013-01-31 8:13 PM, Angela Barone wrote:
Hello,
I'm trying to write a select query that grabs two prices from my db and
displays them on a web page. I want it to grab the `listprice`, and either the
`unitprice` or the `specialprice` depending on the following criteria
Hi Neil,
On 11/22/2012 7:14 PM, h...@tbbs.net wrote:
2012/11/22 14:30 +, Neil Tompkins
I'm struggling with what I think is a basic select but can't think how to
do it : My data is
id,type
1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1
From this I what to get a distinct list of id
`test`.`atest` (
`id` int(10) unsigned NOT NULL,
`type` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
insert into atest(id,type)
values(1000,5)
,(1001,5)
,(1002,2)
,(1001,2)
,(1003,2)
,(1005,2)
,(1006,1);
SELECT DISTINCT id
FROM
Hi,
I'm struggling with what I think is a basic select but can't think how to
do it : My data is
id,type
1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1
From this I what to get a distinct list of id where the type equals 2 and 5
Any ideas ?
Neil
SELECT DISTINCT id FROM table WHERE type IN ('2','5')
should work
On 22 November 2012 14:30, Neil Tompkins neil.tompk...@googlemail.comwrote:
Hi,
I'm struggling with what I think is a basic select but can't think how to
do it : My data is
id,type
1000,5
1001,5
1002,2
1001,2
1003,2
Hi Neil
Would something like this work.
SELECT DISTINCT id,type from your_table WHERE type=2 OR type=5;
Mike
- Original Message -
From: Neil Tompkins neil.tompk...@googlemail.com
To: [MySQL] mysql@lists.mysql.com
Sent: Thursday, November 22, 2012 9:30 AM
Subject: Basic SELECT help
Basically I only what to return the IDs that have both types.
On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski marek.gutow...@gmail.comwrote:
SELECT DISTINCT id FROM table WHERE type IN ('2','5')
should work
On 22 November 2012 14:30, Neil Tompkins neil.tompk...@googlemail.comwrote:
Hi
response did not go to the list..
I assume that you mean the id must be associated with both type=5 AND
type=2 as opposed to type=5 OR type=2;
in some dialect of SQL (not mysql) you can do this:
select distinct id from 'table' where type=5
intersect
select distinct id from 'table' where type=2
U can remove the type field it will work
On Nov 22, 2012 8:21 PM, Neil Tompkins neil.tompk...@googlemail.com
wrote:
Basically I only what to return the IDs that have both types.
On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski marek.gutow...@gmail.com
wrote:
SELECT DISTINCT id FROM table
How about if I have the following
SELECT DISTINCT id
FROM my_table
WHERE (type = 3 OR type = 28 OR type = 1)
In this instance, for the id 280149 it only has types 3 and 28 but *not *1.
But using the OR statement returns id 280149
On Thu, Nov 22, 2012 at 2:53 PM, Benaya Paul benayap
as opposed to type=5 OR type=2;
in some dialect of SQL (not mysql) you can do this:
select distinct id from 'table' where type=5
intersect
select distinct id from 'table' where type=2
As INTERSECT is not avilable under mysql, we will have to go the JOIN route
select distinct a.id from mytable
Keep joining I think. In the absence of intersect (which incurs the cost of
a query per type anyhow ), this join pattern is the only option I can think
of.
On 2012-11-22 10:01 AM, Neil Tompkins neil.tompk...@googlemail.com
wrote:
Michael,
Thanks this kind of works if I'm checking two types.
SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;
On 22 November 2012 15:01, Neil Tompkins neil.tompk...@googlemail.com wrote:
Michael,
Thanks this kind of works if I'm checking two types. But what about if I
have 5 types ?
On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman mdyk
Do you know if I had multiple joins there would be a performance issue ?
On Thu, Nov 22, 2012 at 3:06 PM, Michael Dykman mdyk...@gmail.com wrote:
Keep joining I think. In the absence of intersect (which incurs the cost
of a query per type anyhow ), this join pattern is the only option I can
Ah read it quickly and misread your requirement. Joins are likely FTW
here. The alternative would be to do something like this, but I'd opt
for the joins if you have a reasonably sized data set:
SELECT id, GROUP_CONCAT(type ORDER BY type) AS typelist FROM mytable
WHERE id IN(x,y,z) GROUP BY id
is indexed, yes?)
As you no doubt have noticed, the problem with these solutions:
SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;
is that they returns ids that have ANY of those values which is not
what you are asking for,
If your dataset is HUGE, there might be a performance problem
:
SELECT id, GROUP_CONCAT(type ORDER BY type) AS typelist FROM mytable
WHERE id IN(x,y,z) GROUP BY id HAVING listid = 'x,y,z';
On 22 November 2012 15:10, Ben Mildren ben.mild...@gmail.com wrote:
SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;
On 22 November 2012 15:01, Neil Tompkins
Having watched responses go back and forth, I'll throw my cave-man approach
into the mix.
select id from
(select distinct id, count(*)
from my_table
where type in (2,5)
group by id
having count(*) = 2)a;
And addressing one of your concerns about more than two variables...in this
example,you
Assuming that (id,type) is unique in the source data, that is a pretty
elegant method:
select id from
(select distinct id, count(*)
from my_table
where type in (2,5)
group by id
having count(*) = 2)a;
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL
method:
select id from
(select distinct id, count(*)
from my_table
where type in (2,5)
group by id
having count(*) = 2)a;
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
When trying this query I get
FUNCTION id does not exist
On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman mdyk...@gmail.com wrote:
select id from
(select distinct id, count(*)
from my_table
where type in (2,5)
group by id
having count(*) = 2)a;
Ignore that it does work fine. Sorry
On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman mdyk...@gmail.com wrote:
Assuming that (id,type) is unique in the source data, that is a pretty
elegant method:
select id from
(select distinct id, count(*)
from my_table
where type in (2,5)
group
Doing a EXPLAIN on the SELECT statement it is using Using where; Using
temporary; Using filesort with 14000 rows of data. How best to improve
this; when I already have indexed on id and type
On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman mdyk...@gmail.com wrote:
Assuming that (id,type
On Thu, Nov 22, 2012 at 11:58 AM, Neil Tompkins
neil.tompk...@googlemail.com wrote:
By unique you mean that no id and type would be duplicated like
1,1
1,1
Yes it isn't possible for duplicate id and type in more than 1 row
Yes, that's exactly what I meant.
- mdyk...@gmail.com
May the
Hmmm.
OR, IN and HAVING pops up.
On Thu, November 22, 2012 15:30, Neil Tompkins wrote:
Hi,
I'm struggling with what I think is a basic select but can't think how to
do it : My data is
id,type
1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1
From this I what to get a distinct
.
All necessary information to provide a sufficient help.
On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski
marek.gutow...@gmail.comwrote:
SELECT DISTINCT id FROM table WHERE type IN ('2','5')
should work
On 22 November 2012 14:30, Neil Tompkins
neil.tompk...@googlemail.comwrote:
Hi,
I'm
information to provide a sufficient help.
On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski
marek.gutow...@gmail.comwrote:
SELECT DISTINCT id FROM table WHERE type IN ('2','5')
should work
On 22 November 2012 14:30, Neil Tompkins
neil.tompk...@googlemail.comwrote:
Hi,
I'm struggling with what
2012/11/22 14:30 +, Neil Tompkins
I'm struggling with what I think is a basic select but can't think how to
do it : My data is
id,type
1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1
From this I what to get a distinct list of id where the type equals 2 and 5
Any ideas ?
This ugly one
2012/11/22 14:30 +, Neil Tompkins
I'm struggling with what I think is a basic select but can't think how to
do it : My data is
id,type
1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1
From this I what to get a distinct list of id where the type equals 2 and 5
Any ideas ?
This ugly one
On 11/22/2012 04:10 PM, Ben Mildren wrote:
SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;
Ben you were almost there ;)
SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id HAVING COUNT(id)=num of
params
The only bad is the hardcoded parameter in the HAVING, may be it might
into atest(id,type)
values(1000,5)
,(1001,5)
,(1002,2)
,(1001,2)
,(1003,2)
,(1005,2)
,(1006,1);
SELECT DISTINCT id
FROM atest
WHERE `type` = 2 OR `type` = 5
GROUP BY id
HAVING count(DISTINCT `type`) = 2;
On Thu, November 22, 2012 22:16, Michael Dykman wrote:
Mogens,
Platform could not be less
Claudio
This is the solution i decided to go for as provided in a previous response.
Thanks
Neil
On 23 Nov 2012, at 00:41, Claudio Nanni claudio.na...@gmail.com wrote:
On 11/22/2012 04:10 PM, Ben Mildren wrote:
SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;
Ben you were almost
-
From: Martin Gainty [mailto:mgai...@hotmail.com]
Sent: 24 September 2012 04:58
To: fuller.art...@gmail.com; pownall...@gmail.com
Cc: mysql@lists.mysql.com
Subject: RE: How to block SELECT * FROM table; but not SELECT * FROMT
table WHERE...;
Possibly run your constructed query thru
Even if you could block them, they would be easy to get around:
SELECT * FROM tbl WHERE 1;
If you have long running queries, you should investigate the reasons (other
than lack of WHERE).
* MyISAM locks the table for any writes. This prevents a SELECT from starting
or a select can prevent
Helo,
Just wondering if is possible to block SELECT queries that doesnt have
a WHERE statement within.
LD
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Am 23.09.2012 20:23, schrieb Luis Daniel Lucio Quiroz:
Just wondering if is possible to block SELECT queries that doesnt have
a WHERE statement within.
no and the idea is broken by design
what is wrong with a select * from table with small
tbales having only a handful of records?
how
select * from table where column=value means it will return only rows that
match. as long as you have proper indexing there should not be any issues.
On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz
luis.daniel.lu...@gmail.com wrote:
Helo,
Just wondering if is possible to block
since 10 years
* mainmenu: select id,title from table order by sortfield;
* and YES in the backend the is no single reson to reduce the result
* and NO using a key would not make anything faster
so why would there be a WHERE make any sense and why does
someone like to break the application just
Tim,
I think you misunderstood the question. Daniel wants to block Select
queries that ask for all rwows, and permit only queries that ask for some
rows, as restricted by the Where clause.
Unfortunately, I don't think that can be done. But I'm not certain of that;
there might be a trick.
Arthur
Possibly run your constructed query thru a regex expression e.g.
String mydata = SELECT * from table WHERE ab;;
Pattern pattern = Pattern.compile('WHERE');
Matcher matcher = pattern.matcher(mydata);
if (matcher.find())
{
//WHERE clause found proceed normally
}
else throw new Exception(WHERE
the mysql query optimizer is somehow stupid
a simple query, order by with a indexed column and
you have to use where order_by_field0 - why the
hell is mysqld not happy that a key is on the field
used in order by?
mysql EXPLAIN SELECT * FROM cms1_quickbar_groups ORDER BY qg_sort ASC
;
+--++--+
| Query_ID | Duration | Query
|
+--++--+
|1 | 0.32261700 | SELECT SQL_NO_CACHE
|
+--++--+
|1 | 0.32261700 | SELECT SQL_NO_CACHE * FROM
cms1_quickbar_groups force key (qbq_key) ORDER BY qg_sort ASC |
|2 | 0.24592100 | SELECT SQL_NO_CACHE * FROM
cms1_quickbar_groups ORDER BY qg_sort ASC
column used in the order by caluse, should be the first column in the
select statement to make the index work
On Wed, Jul 11, 2012 at 3:16 PM, Reindl Harald h.rei...@thelounge.netwrote:
Am 11.07.2012 11:43, schrieb Ewen Fortune:
Hi,
On Wed, Jul 11, 2012 at 10:31 AM, Reindl Harald h.rei
|
+--++--+
|1 | 0.32261700 | SELECT SQL_NO_CACHE * FROM
cms1_quickbar_groups force key (qbq_key) ORDER BY qg_sort ASC |
|2 | 0.24592100 | SELECT SQL_NO_CACHE * FROM
my reason for create a key on qg_sort was primary
for this query - but why is here 'filesort' used?
mysql EXPLAIN SELECT * FROM cms1_quickbar_groups ORDER BY qg_sort ASC;
++-+--+--+---+--+-+--+--++
| id
retrieve specific columns on which indexes are created to use the feature
of Covering index.
On Wed, Jul 11, 2012 at 3:19 AM, Reindl Harald h.rei...@thelounge.netwrote:
my reason for create a key on qg_sort was primary
for this query - but why is here 'filesort' used?
mysql EXPLAIN SELECT * FROM
Hello
COMMIT statements may or may not force the database to call fflush() to flush
your double-write to disk. This may or may not affect your performance,
depending on your scale, traffic, and how much you're trying to squeeze your
hardware. If you're working on the borderline like I am,
INTEGER);
Client 1:
SELECT * FROM foo WHERE i = 0;
SELECT * FROM foo WHERE i = 0;
Client 2:
UPDATE foo SET j = 1 WHERE i = 0;
Suppose the table starts out with a single tuple (0, 0). Now, if client 1
and client 2 are running at the same time, wrapping client 1's select
statements with a BEGIN/COMMIT
We have an application with blocks of code that begin with setting autocommit
off, and end with a commit. The code in between does only selects, no updating.
1) Am I correct in thinking that the autocommit and commit statements
don't really accomplish anything useful?
2) If the
I vote 1) yes 2) no
It could be result of the app developer's convenience to just wrap anything
they submit to the database in a transaction. Selects are not transaction but
autocommit/commit do no harm. That might be the thinking.
On 09.04.2012, at 11:38, Rozeboom, Kay [DAS] wrote:
We
2012/03/01 19:56 -0800, Don Wieland
I do not get the same results. Am I missing something? Hopefully
something simple ;-)
O, you are. You do not want GROUP_CONCAT in the subquery. It gives you the
comma-separated string whereto you referred, which, as far as the IN goes, is
only one string
a result
if one or more of the payees are different than the invoice's client_id.
So now with the mySQL queries that are working:
First of all, the client_id of the invoice I am querying on is 251719.
query 1 = select group_concat(payment_id) from tl_trans_pmt_items
where inv_id = 1033911
I'm not sure your method isn't working, but try changing changing the to
date part to '2012-02-08' and see what you get.
HTH,
Arthur
1 - 100 of 4787 matches
Mail list logo
