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 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=gcdmg-mysql-2@m.gmane.org

RE: concatenate sql query with group by and having

With some databases such as MySQL=2C subqueries have to be explicitly named=
..=A0 For example

select * from (select * from (select * from table) sub1) sub2=3B

If not=2C you will see an error like: "ERROR 1248 (42000): Every derived ta=
ble must have its own alias"

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

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

----------------------------------------
> Date: Wed=2C 28 Jul 2010 11:10:32 -0500
> Subject: concatenate sql query with group by and having
> From: pengyu.ut@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=2C provided that for any rows
> that have the same symbol=2C chrom and strand should be the same
> (basically=2C 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=2C name as refSeq=2C chrom=2C strand=2C txStart=
from
> refFlat group by refSeq having count(*)=3D1=3B
>
>
> I think that something like
>
> SELECT name FROM (SELECT name=2C type_id FROM (SELECT * FROM foods))=3B
>
> 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=2C name as refSeq=2C chrom=2C
> strand=2C txStart from refFlat group by refSeq having count(*)=3D1)=3B
>
> --
> Regards=2C
> Peng
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dtravis_ard@hotmail.c=
om
>
=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg