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 mog...@fumlersoft.dkwrote:
Ok, to make up for my bad joke, here's the answer
to the original question.
DROP TABLE IF EXISTS `test`.`atest`;
CREATE TABLE
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,
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
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...@gmail.com 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
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
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
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
*HAVING typelist = 'x,y,z';
On 22 November 2012 15:25, Ben Mildren ben.mild...@gmail.com 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:
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
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 mdyk...@gmail.com wrote:
Assuming that (id,type) is unique in the source data, that is a pretty
elegant
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) is
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
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.
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
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 be
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
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
29 matches
Mail list logo
