RE: concatenate sql query with group by and having

y refSeq having count(*) = 1; > Date: Wed, 28 Jul 2010 11:10:32 -0500 > Subject: concatenate sql query with group by and having > From: pengyu...@gmail.com > To: mysql@lists.mysql.com > > mysql -ugenome -hgenome-mysql.cse.ucsc.edu mm9 -A

concatenate sql query with group by and having

mysql -ugenome -hgenome-mysql.cse.ucsc.edu mm9 -A I start mysql with the above command. Then I want to select the rows from the result of the following query, provided that for any rows that have the same symbol, chrom and strand should be the same (basically, discard the rows that have the same s

RE: Query with group by

Hi Michael! Talking about the "query with group by issue"... > I'll explain my reasoning below. [...] > From the manual, section "7.2.8 How MySQL Optimizes LEFT > JOIN and RIGHT > JOIN" <http://dev.mysql.com/doc/mysql/en/LEFT_JOIN_o

Re: Query with group by

ast one of them ;-) Rhino - Original Message - From: "Michael Stassen" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Cc: "Jose Miguel Pérez" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, September

Re: Query with group by

I took a closer look and found the problem -- char(date). Is char() necessary in DB2 to cast the date as a string? In MySQL, char() expects a list of integers to interpret as a list of character codes . Removing char() makes the query

Re: Query with group by

Jose Miguel Pérez wrote: Hi Michael! Yes, you're right, thanks for extending and clarifying my message. However, I'm not confident about your comments on the inefficiency for the following query: I'll explain my reasoning below. SELECT c1.date, c1.location, c1.version FROM cities

Re: Query with group by

- As you can see, both queries worked and produced the same result in DB2, aside from the row sequence, which is easily fixable via an Order By. I'm at a loss to explain why my query didn't work in MySQL V4.1.4. Rhino - Original

Re: Query with group by

Rhino wrote: I agree that Michael's solution with the temporary tables is the best I have seen so far. I can't take much credit. It's just an adaptation of the solution in the manual. I am running MySQL 4.0.15 so I don't have any subquery capability. As a result, I went to DB2 to come up with on

Re: Query with group by

Hi Michael! Yes, you're right, thanks for extending and clarifying my message. However, I'm not confident about your comments on the inefficiency for the following query: > > SELECT c1.date, c1.location, c1.version > > FROM cities c1 > > LEFT JOIN cities c2 > >

Re: Query with group by

>Which version of MySQL are you running? > >I'm having trouble thinking of a solution that doesn't involve a subquery >but subqueries aren't supported until version 4.1.x; I don't want to give >you a subquery if you can't run it. > >Rhino I'm running 3.23.43. -- Vincent -- MySQL General Mai

Re: Query with group by

e - From: "Michael Stassen" <[EMAIL PROTECTED]> To: "Jose Miguel Pérez" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, September 16, 2004 10:47 AM Subject: Re: Query with group by > > Jose Miguel Pérez wrot

Re: Query with group by

Jose Miguel Pérez wrote: Hi Vincent! i'm trying to built a query that report me the latest tuple for a given field. I explain myself a bit. Let's say i have a table such this : There is no simple solution to your problem. Strictly speaking, it's forbidden to name a column in the SELECT clause w

Re: Query with group by

Hi Vincent! > i'm trying to built a query that report me the latest tuple for a given > field. > I explain myself a bit. Let's say i have a table such this : There is no simple solution to your problem. Strictly speaking, it's forbidden to name a column in the SELECT clause which is NOT on th

Re: Query with group by

- Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, September 16, 2004 3:05 AM Subject: Query with group by > Hello all, > > i'm trying to built a query that report me the latest tuple for a given > field. > I explain

Query with group by

Hello all, i'm trying to built a query that report me the latest tuple for a given field. I explain myself a bit. Let's say i have a table such this : +++-+--+-+ | id | date | content | location | version | +++-+--+

Re: Nested query with GROUP BY ... HAVING COUNT(*) ... hangs 4.1.3 beta

Thanks for your response. >>> <[EMAIL PROTECTED]> 7/19/2004 11:47:39 AM >>> >It looks like your IN statement is forcing your inner SELECT to execute >once PER ROW of your main table. It is asking the engine to make sure that >_each and every_ id value in main meets the condition in the inner >

Re: Nested query with GROUP BY ... HAVING COUNT(*) ... hangs 4.1.3 beta

It looks like your IN statement is forcing your inner SELECT to execute once PER ROW of your main table. It is asking the engine to make sure that _each and every_ id value in main meets the condition in the inner select. So, for each and every value in the table main, it has to re-computing t

Nested query with GROUP BY ... HAVING COUNT(*) ... hangs 4.1.3 beta

OS: Win2k Server MySQL: 4.1.1 alpha / 4.1.3 beta Table type: INNO DB In my production environment, running the query: select * from main where id in (select main_id from receipt group by main_id having COUNT(*) > 5) will hang the server - sometimes for over a day, thugh it seems it will event