Hi, you guys helped me a lot the last time so i'd like to ask you again for the solution to one problem. The table msg_bc contains numbers and timestamps like this CREATE TABLE `emeris`.`msg_bc` ( `id` bigint(20) unsigned NOT NULL auto_increment COMMENT 'Automatically incremented ID value', `timestamp` datetime NOT NULL COMMENT 'Message generation timestamp', `barcode` bigint(20) unsigned NOT NULL default '0' COMMENT 'ID of transport vessel', PRIMARY KEY (`id`), KEY `ts_ind` (`timestamp`) ) Ok, i like to fetch rows from msg_bc where the timestamp is between a start and a stop time. No problem with that. It returns zero to four rows. But i need the field barcode from this zero to four rows combined in one row of a table like this: CREATE TABLE `emeris`.`tmp` ( `id` int(11) NOT NULL auto_increment, `bc1` bigint(20) default NULL, `bc2` bigint(20) default NULL, `bc3` bigint(20) default NULL, `bc4` bigint(20) default NULL, PRIMARY KEY (`id`) ) My first try looks like this: select lb.stop as `timestamp`, lb.bc1, lb.bc2, lb.bc3, lb.bc4, r.vessel from ( select lb1.`timestamp` as start, ( select min(`timestamp`) from msg_lbh as lb2 where lb2.`timestamp` >= lb1.`timestamp` ) as stop, ( select barcode from msg_bc as bc WHERE bc.`timestamp` between start and stop AND bc.device = 75 limit 0,1 ) as bc1, ( select barcode from msg_bc as bc WHERE bc.`timestamp` between start and stop AND bc.device = 75 limit 1,1 ) as bc2, ( select barcode from msg_bc as bc WHERE bc.`timestamp` between start and stop AND bc.device = 75 limit 2,1 ) as bc3, ( select barcode from msg_bc as bc WHERE bc.`timestamp` between start and stop AND bc.device = 75 limit 3,1 ) as bc4 from msg_lbl as lb1) as lb but i don't like the four "( select barcode... ) as bc* " lines. Is there a more performant way to do what i like to do? Thanks
You are right. It's not T-SQL Syntax. I`m so sorry to bother you with my stupid MySQL based question.
Not a stupid question, this is just not the right place to post it - we focus on Microsoft SQL Server.[]
Thanks, you guys helped me a lot with another (mysql based) problem. So i thought you could help me again with some basic informations about the problem. In the meantime some other guy gave some hints about using cursors and optimizing the query. (btw. that guy only works at oracle databases ;-) )
No i haven`t. With the help of another guy we rebuild the whole query. With the use of cursors we build a query which scales linear instead of my old solution.
[quote user="jhomann"] No i haven`t. With the help of another guy we rebuild the whole query. With the use of cursors we build a query which scales linear instead of my old solution. [/quote] Ok. Fine