Hi!
I have a "SQL query construction" question that I hope someone can help
me with. After comparing a bunch of DNA fragments (see name below) with
a larger reference sequence I get a ordered list ranked according to
similarities, and with start/stop co-ordinates where the fragments map
to the reference sequence:
+------+------+-------+------+----------+
| name | rank | start | stop | sub_rank |
+------+------+-------+------+----------+
| A | 1 | 1 | 1000 | NULL |
| B | 2 | 2 | 998 | NULL |
| C | 4 | 1100 | 2000 | NULL |
| D | 3 | 3050 | 4100 | NULL |
| E | 5 | 2040 | 3000 | NULL |
| F | 6 | 1102 | 2000 | NULL |
| G | 7 | 1098 | 1998 | NULL |
| H | 8 | 3048 | 4100 | NULL |
| I | 9 | 3051 | 4102 | NULL |
+------+------+-------+------+----------+
A graphical representation of fragments mapped to the ref sequence:
ref========================================>
1 A------>
2 B---->
3 D------>
4 C------>
5 E---->
6 F------->
7 G------->
8 H------->
9 I------->
Now, I want to group fragments in each overlapping position and sub-rank
them according to their rank in that position. The final table would
then look like:
+------+------+-------+------+----------+
| name | rank | start | stop | sub_rank |
+------+------+-------+------+----------+
| A | 1 | 1 | 1000 | 1 |
| B | 2 | 2 | 998 | 2 |
| C | 4 | 1100 | 2000 | 1 |
| D | 3 | 3050 | 4100 | 1 |
| E | 5 | 2040 | 3000 | 1 |
| F | 6 | 1102 | 2000 | 2 |
| G | 7 | 1098 | 1998 | 3 |
| H | 8 | 3048 | 4100 | 2 |
| I | 9 | 3051 | 4102 | 3 |
+------+------+-------+------+----------+
Is this possible to achieve using SQL queries alone (perhaps with GROUP
BY, nested SELECTs etc)?
I've managed to do this with a Perl-DBI script, but would much prefer to
do it completely with MySQL instead. The Perl code is below and below
that is the MySQL-dump of the test data set...
Many thanks in advance!
Marcus
while ( [at] {$dbh->selectcol_arrayref("SELECT rank FROM test WHERE sub_rank
IS NULL")}) {
[at] null_sub_ranks = [at] {$dbh->selectcol_arrayref("SELECT rank FROM test
WHERE sub_rank IS NULL AND NOT (start>=(SELECT stop FROM test WHERE rank
= (SELECT min(rank) FROM test WHERE sub_rank IS NULL)) OR stop <=
(SELECT start FROM test WHERE rank = (SELECT min(rank) FROM test WHERE
sub_rank IS NULL)))")};
for ($rank=0; $rank < scalar( [at] null_sub_ranks); $rank++ ) {
$sub_rank = $rank + 1;
$dbh->do("UPDATE test SET sub_rank=$sub_rank WHERE rank=
$null_sub_ranks[$rank]");
}
}
-- MySQL dump 10.10
--
-- Host: localhost Database: bxb
-- ------------------------------------------------------
-- Server version 5.0.22
/*!40101 SET [at] OLD_CHARACTER_SET_CLIENT= [at] [at] CHARACTER_SET_CLIENT */;
/*!40101 SET [at] OLD_CHARACTER_SET_RESULTS= [at] [at] CHARACTER_SET_RESULTS */;
/*!40101 SET [at] OLD_COLLATION_CONNECTION= [at] [at] COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET [at] OLD_TIME_ZONE= [at] [at] TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET [at] OLD_UNIQUE_CHECKS= [at] [at] UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET [at] OLD_FOREIGN_KEY_CHECKS= [at] [at] FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET [at] OLD_SQL_MODE= [at] [at] SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
*/;
/*!40111 SET [at] OLD_SQL_NOTES= [at] [at] SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`name` text,
`rank` int(11) default NULL,
`start` int(11) default NULL,
`stop` int(11) default NULL,
`sub_rank` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `test`
--
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES
('A',1,1,1000,NULL),('B',2,2,998,NULL),('C',4,1100,2000,NULL ),('D',3,3050,4100,NULL),('E',5,2040,3000,NULL),('F',6,1102, 2000,NULL),('G',7,1098,1998,NULL),('H',8,3048,4100,NULL),('I ',9,3051,4102,NULL);
UNLOCK TABLES;
/*!40000 ALTER TABLE `test` ENABLE KEYS */;
/*!40103 SET TIME_ZONE= [at] OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE= [at] OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS= [at] OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS= [at] OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT= [at] OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS= [at] OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION= [at] OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES= [at] OLD_SQL_NOTES */;
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules [at] m .gmane.org
