SQL Server Performance

Fetch fields from several rows into one row

Discussion in 'Getting Started' started by jhomann, Jun 17, 2009.

  1. jhomann New Member

    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
  2. Adriaan New Member

    Doesn't look like T-SQL syntax to me.
  3. jhomann New Member

    You are right. It's not T-SQL Syntax. I`m so sorry to bother you with my stupid MySQL based question.
  4. Adriaan New Member

    Not a stupid question, this is just not the right place to post it - we focus on Microsoft SQL Server.[;)]
  5. Madhivanan Moderator

    Post at www.mysql.com
  6. Madhivanan Moderator

    By seeing the query, it seems you need to Make use of Group_concat function
  7. jhomann New Member

    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 ;-) )
  8. Madhivanan Moderator

    Did you try using group_concat() function?
  9. jhomann New Member

    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.
  10. Madhivanan Moderator

    [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

Share This Page