List:General Discussion« Previous MessageNext Message »
From:Travis Ard Date:July 29 2010 6:01pm
Subject:RE: concatenate sql query with group by and having
View as plain text  
With some databases such as MySQL, subqueries have to be explicitly named.  For
example

select * from (select * from (select * from table) sub1) sub2;

If not, you will see an error like: "ERROR 1248 (42000): Every derived table must have its
own alias"

If
 I understand your problem correctly, you are looking to limit your 
result set to only those records that have symbols with a single unique 
combination of chrom, and strand.  If that's correct, something like the
 query below might work:

select geneName as symbol, name as refSeq, chrom, strand, txStart 
from refFlat 
where geneName in 
-- returns all geneNames (symbols) with one unique combination of chrom and strand
(select geneName from
-- returns all unique combinations of symbol, chrom, and strand
(select distinct geneName, chrom, strand
from refFlat) sub1
group by geneName
having count(*) = 1) 
group by refSeq
having count(*) = 1;

----------------------------------------
> Date: Wed, 28 Jul 2010 11:10:32 -0500
> Subject: concatenate sql query with group by and having
> From: pengyu.ut@stripped
> To: mysql@stripped
>
> 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 symbols but different
> chrom and strand). Could anybody show me how to do it?
>
> select geneName as symbol, name as refSeq, chrom, strand, txStart from
> refFlat group by refSeq having count(*)=1;
>
>
> I think that something like
>
> SELECT name FROM (SELECT name, type_id FROM (SELECT * FROM foods));
>
> works for sqlite3 (in terms of syntax). But the following do not work
> for mysql. Is this a difference between mysql and sqlite3? (I'm always
> confused by the difference between different variants of SQL)
>
> select * from (select geneName as symbol, name as refSeq, chrom,
> strand, txStart from refFlat group by refSeq having count(*)=1);
>
> --
> Regards,
> Peng
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
 		 	   		  
Thread
concatenate sql query with group by and havingPeng Yu28 Jul
  • RE: concatenate sql query with group by and havingTravis Ard29 Jul