subject:"RE\: Help with SQL query construction"

RE: Help with SQL query construction

2007-12-03 Thread Jeff Mckeon



 -Original Message-
 From: Marcus Claesson [mailto:[EMAIL PROTECTED]
 Sent: Monday, December 03, 2007 7:49 AM
 To: mysql@lists.mysql.com
 Subject: Help with SQL query construction
 
 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 (@{$dbh-selectcol_arrayref(SELECT rank FROM test WHERE sub_rank
 IS NULL)}) {
 @null_sub_ranks = @{$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(@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: localhostDatabase: bxb
 -- --
 -- Server version   5.0.22
 
 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 /*!40101 SET NAMES utf8 */;
 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
 /*!40103 SET TIME_ZONE='+00:00' */;
 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
 FOREIGN_KEY_CHECKS=0 */;
 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
 */;
 /*!40111 SET @OLD_SQL_NOTES=@@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`
 --
 
 
 /*!4 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,30
 50,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;
 /*!4 ALTER TABLE `test` ENABLE KEYS */;
 /*!40103 SET [EMAIL PROTECTED] */;
 
 /*!40101 SET [EMAIL PROTECTED] */;
 /*!40014 SET [EMAIL PROTECTED] */;
 /*!40014 SET [EMAIL PROTECTED] */;
 /*!40101 SET [EMAIL PROTECTED] */;
 /*!40101 SET [EMAIL PROTECTED] */;
 /*!40101 SET [EMAIL PROTECTED] */;
 /*!40111 SET [EMAIL PROTECTED] */;
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]


I'd say perl is

Re: Help with SQL query construction

2007-12-03 Thread Peter Brawley


Marcus,

I've managed to do this with a Perl-DBI script, but 
would much prefer to do it completely with MySQL instead. 


You could port it to a recursive stored procedure. It would probably be 
slower, and what would you have gained?


PB

Marcus Claesson wrote:

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 (@{$dbh-selectcol_arrayref(SELECT rank FROM test WHERE sub_rank
IS NULL)}) {
@null_sub_ranks = @{$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(@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: localhostDatabase: bxb
-- --
-- Server version   5.0.22

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
*/;
/*!40111 SET @OLD_SQL_NOTES=@@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`
--


/*!4 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;
/*!4 ALTER TABLE `test` ENABLE KEYS */;
/*!40103 SET [EMAIL PROTECTED] */;

/*!40101 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40111 SET [EMAIL PROTECTED] */;


  


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Help with SQL query construction

Re: Help with SQL query construction

2 matches

Site Navigation

Mail list logo

Footer information