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 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 there ;)
>
> SELECT id
I'm using mysql version 5.1.66 on ubuntu and can not add a trigger any more.
When trying to add a trigger In phpmyadmin I get "Your SQL query has been
executed successfully" but when I issue the command show triggers; no triggers
are listed.
Im logged into mysql as root and Im not sure why I
Ok, to make up for my bad joke, here's the answer
to the original question.
DROP TABLE IF EXISTS `test`.`atest`;
CREATE TABLE `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
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)=
The only bad is the hardcoded parameter in the HAVING, may be it might be
improved.
Anywa
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 ?
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 ?
Mogens,
Platform could not be less relevant to a question of MySql syntax.
The techniques we have been discussing have been available to every
version of MySql post v3.23 and the class/job function he is applying
it to is neither relevant to the problem nor any of our business,
unless he volunteer
On Thu, November 22, 2012 15:45, Neil Tompkins wrote:
> Basically I only what to return the IDs that have both types.
>
And that's exactly what below statement will return.
You forgot to include what platform you are on,
which version of MySQL you are running and
what class you are attending.
A
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
On Thu, Nov 22, 2012 at 11:58 AM, Neil Tompkins
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 Source be with you.
--
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 wrote:
> Assuming that (id,type) is unique in the so
Ignore that it does work fine. Sorry
On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman 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 by id
When trying this query I get
FUNCTION id does not exist
On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman wrote:
> select id from
> > (select distinct id, count(*)
> > from my_table
> > where type in (2,5)
> > group by id
> > having count(*) = 2)a;
>
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
On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman wrote:
> Assuming that (id,type) is unique in the source data, that is a pretty
> elegant method:
>
> > se
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.
--
MySQ
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 wou
*HAVING typelist = 'x,y,z';
On 22 November 2012 15:25, Ben Mildren wrote:
> 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
Of course there is a cost for the join, each link being a distinct
lookup query but that is the same cost the INTERSECT would impose.
It is not a bad as multiple joins generally might be as all the
lookups are against the same key in the same table which should keep
that index in ram. (type is ind
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 H
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 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
> think of.
>
> O
SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;
On 22 November 2012 15:01, Neil Tompkins 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 wrote:
>
>> response did not go t
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"
wrote:
Michael,
Thanks this kind of works if I'm checking two types. But what about if I
have 5 ty
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 wrote:
> 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
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 wrote:
> U ca
U can remove the type field it will work
On Nov 22, 2012 8:21 PM, "Neil Tompkins"
wrote:
> Basically I only what to return the IDs that have both types.
>
>
> On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski >wrote:
>
> > SELECT DISTINCT id FROM table WHERE type IN ('2','5')
> >
> > should work
>
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
Basically I only what to return the IDs that have both types.
On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski wrote:
> SELECT DISTINCT id FROM table WHERE type IN ('2','5')
>
> should work
>
>
> On 22 November 2012 14:30, Neil Tompkins wrote:
>
>> Hi,
>>
>> I'm struggling with what I think is a
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"
To: "[MySQL]"
Sent: Thursday, November 22, 2012 9:30 AM
Subject: Basic SELECT help
Hi,
I'm struggling with what I think is a
SELECT DISTINCT id FROM table WHERE type IN ('2','5')
should work
On 22 November 2012 14: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
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
DAC for MySQL is direct access components for MySQL, EnterpriseDB. It
allows you to create Delphi/C++Builder applications with direct access to
DAC for MySQL/EnterpriseDB without BDE and ODBC.
*What’s new in v2.8.0:*
Version 2.8.0 brings support for Rad Studio XE3, introduces some new
features an
Something like:
select aname,max(adate)
group by aname;
On Thu, November 22, 2012 11:06, sagar bs wrote:
> Hi all,
>
>
> I have the table with two columns(account_name and order_date). In the
> account_name col, some account names are only once and few account names
> are twice and few others are
- Original Message -
> From: "Cabbar Duzayak"
>
> Is there a way of looking at how mysql builds the query plan and
> executes it for a given query? EXPLAIN is definitely a useful tool, but it is
> not exact (shows approximations as far as I can see), furthermore I want
> something like ho
Is this a joke?
On Thu, Nov 22, 2012 at 10:20 AM, Zhigang Zhang wrote:
> By experience!
>
> -Original Message-
> From: Cabbar Duzayak [mailto:cab...@gmail.com]
> Sent: Thursday, November 22, 2012 3:13 PM
> To: mysql@lists.mysql.com
> Subject: Query Plan Analyzer
>
> Hi All,
>
> Is there a
By experience!
-Original Message-
From: Cabbar Duzayak [mailto:cab...@gmail.com]
Sent: Thursday, November 22, 2012 3:13 PM
To: mysql@lists.mysql.com
Subject: Query Plan Analyzer
Hi All,
Is there a way of looking at how mysql builds the query plan and executes
it for a given query? EXPLA
35 matches
Mail list logo
