SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    to get previous row

    Hi,

    Im lookiing for a query to get previous row

    my table structure is like this

    Code:
    CREATE TABLE `test` (
      `id` INT(5)  NOT NULL AUTO_INCREMENT,
      `service` INT(5)  NOT NULL,
      `timestamp` DATETIME  NOT NULL,
      PRIMARY KEY (`id`)
    )
    ENGINE = MyISAM;
    Code:
    INSERT INTO `test` (`id`, `service`, `timestamp`) VALUES
    (1, 1, '2010-02-21 13:52:37'),
    (2, 2, '2010-02-23 16:52:37'),
    (3, 3, '2010-02-27 20:52:37'),
    (4, 4, '2010-02-18 21:52:37'),
    (5, 3, '2010-03-01 01:52:37'),
    (6, 2, '2010-03-02 13:52:37'),
    (7, 1, '2010-03-03 04:52:47'),
    (8, 4, '2010-03-04 18:52:47'),
    (9, 3, '2010-03-21 13:52:47'),
    (10, 1, '2010-03-08 02:53:01'),
    (11, 2, '2010-03-08 04:53:01'),
    (12, 4, '2010-03-08 15:53:01'),
    (13, 3, '2010-03-08 12:53:03'),
    (14, 2, '2010-03-08 14:53:03'),
    (15, 1, '2010-03-08 03:53:04'),
    (16, 4, '2010-03-08 20:53:13'),
    (17, 1, '2010-03-08 23:53:14'),
    (18, 2, '2010-03-08 13:53:15');

    Code:
    SELECT * FROM test t where `timestamp` BETWEEN '2010-03-08 00:00:00' AND '2010-03-08 23:59:59' order by `service`,`timestamp`
    Results is
    Code:
    id	service	timestamp
    10	1	2010-03-08 02:53:01
    15	1	2010-03-08 03:53:04
    17	1	2010-03-08 23:53:14
    11	2	2010-03-08 04:53:01
    18	2	2010-03-08 13:53:15
    14	2	2010-03-08 14:53:03
    13	3	2010-03-08 12:53:03
    12	4	2010-03-08 15:53:01
    16	4	2010-03-08 20:53:13

    I want to list all service with time stamp between mentioned date time and also previous row less than '2010-03-08 00:00:00' for each service

    my result should be like as given below


    Code:
    id	service	timestamp
    7	1	2010-03-03 04:52:47
    10	1	2010-03-08 02:53:01
    15	1	2010-03-08 03:53:04
    17	1	2010-03-08 23:53:14
    6	2	2010-03-02 13:52:37
    11	2	2010-03-08 04:53:01
    18	2	2010-03-08 13:53:15
    14	2	2010-03-08 14:53:03
    5	3	2010-03-01 01:52:37
    13	3	2010-03-08 12:53:03
    8	4	2010-03-04 18:52:47
    12	4	2010-03-08 15:53:01
    16	4	2010-03-08 20:53:13
    can anyone help me with writing a query to get result as above.

    thanks in advance

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    696
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select id,service,`timestamp` 
      from test 
     where `timestamp` between '2010-03-08 00:00:00' and '2010-03-08 23:59:59' 
     union all
    select id,service,`timestamp`
      from test t
     where `timestamp` =
         (select max(`timestamp`)
            from test
           where test.service = t.service
             and `timestamp` < '2010-03-08 00:00:00')

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the query its working , i do have a doubt

    my table will be populated around say 50,000(may increase) rows. so will it take
    lot time to execute this query.

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    50000 rows is insignificant. make sure your timestamp columns are indexed though.

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I have given indexing

    its around 25000 datas now and it takes more than 1 minute to execute.

    I'm also trying in my way to optimize,

    hope i will get better suggestions

    Thanks

  6. #6
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have found something like mentioned below which worked fine when dealing with 25000 data , i think i have saved around 1 min using this query.
    Can anyone check if this query is ok?
    Code:
    (select id,service,`timestamp` 
      from test 
      where `timestamp` between '2010-03-08 00:00:00' 
      and 
      '2010-03-08 23:59:59' 
    )
    
    union all
    
    (select id,service,`timestamp`
      from test t
      where (`timestamp`,`service`) IN
         (SELECT `timestamp` , `service`
          FROM (
                  SELECT MAX( a.`timestamp` ) AS `timestamp` , a.`service`
                  FROM `test` a
                  WHERE a.`timestamp` < '2010-03-08 00:00:00'
                  GROUP BY a.`service`
                  ) 
          AS m)
    ) ORDER BY `service` , `timestamp`


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •