List:General Discussion« Previous MessageNext Message »
From:Peng Yu Date:July 28 2010 4:10pm
Subject:concatenate sql query with group by and having
View as plain text  
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
Thread
concatenate sql query with group by and havingPeng Yu28 Jul
  • RE: concatenate sql query with group by and havingTravis Ard29 Jul