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.dk>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` (
> `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 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 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 volunteers to share it. Excepting only the working
> > assumption that he is using a MySql version released in this century,
> > I don't know how this would have informed my analysis or response.
> >
> > - michael dykman
> >
> > On Thu, Nov 22, 2012 at 4:00 PM, Mogens Melander <mog...@fumlersoft.dk>
> > wrote:
> >>
> >> 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.
> >>
> >> All necessary information to provide a sufficient help.
> >>
> >>>
> >>> On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski
> >>> <marek.gutow...@gmail.com>wrote:
> >>>
> >>>> SELECT DISTINCT id FROM table WHERE type IN ('2','5')
> >>>>
> >>>> should work
> >>>>
> >>>>
> >>>> On 22 November 2012 14:30, Neil Tompkins
> >>>> <neil.tompk...@googlemail.com>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 list of id where the type equals 2
> >>>>> and
> >>>>> 5
> >>>>>
> >>>>> Any ideas ?
> >>>>>
> >>>>> Neil
> >>>>>
> >>>>
> >>>>
> >>>
> >>> --
> >>> This message has been scanned for viruses and
> >>> dangerous content by MailScanner, and is
> >>> believed to be clean.
> >>>
> >>>
> >>
> >>
> >> --
> >> Mogens Melander
> >> +66 8701 33224
> >>
> >> --
> >> This message has been scanned for viruses and
> >> dangerous content by MailScanner, and is
> >> believed to be clean.
> >>
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe: http://lists.mysql.com/mysql
> >>
> >
> >
> >
> > --
> > - michael dykman
> > - mdyk...@gmail.com
> >
> > May the Source be with you.
> >
> > --
> > This message has been scanned for viruses and
> > dangerous content by MailScanner, and is
> > believed to be clean.
> >
> >
>
>
> --
> Mogens Melander
> +66 8701 33224
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql
>
>
