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
Hi
Is there is performance issue from this query on more then 5-10 million data
On Fri, Nov 23, 2012 at 11:17 AM, Mogens Melander wrote:
> 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` (
>
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
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
m: Neil Tompkins [mailto:neil.tompk...@googlemail.com]
> Sent: Thursday, November 22, 2012 8:30 AM
> To: [MySQL]
> Subject: Basic SELECT help
>
> Hi,
>
> I'm struggling with what I think is a basic select but can't think how
> to do it : My data is
>
>
*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 struggli
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
31 matches
Mail list logo
