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...@gmail.com > To: mysql@lists.mysql.com > > 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=travis_...@hotmail.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
