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
