To get previous row

Databases
1

Hi,

Im lookiing for a query to get previous row

my table structure is like this


CREATE TABLE `test` (
  `id` INT(5)  NOT NULL AUTO_INCREMENT,
  `service` INT(5)  NOT NULL,
  `timestamp` DATETIME  NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = MyISAM;




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');



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


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


id	service	timestamp
[B][COLOR="Red"]7	1	2010-03-03 04:52:47[/COLOR][/B]
10	1	2010-03-08 02:53:01
15	1	2010-03-08 03:53:04
17	1	2010-03-08 23:53:14
[B][COLOR="Red"]6	2	2010-03-02 13:52:37[/COLOR][/B]
11	2	2010-03-08 04:53:01
18	2	2010-03-08 13:53:15
14	2	2010-03-08 14:53:03
[B][COLOR="Red"]5	3	2010-03-01 01:52:37[/COLOR][/B]
13	3	2010-03-08 12:53:03
[B][COLOR="Red"]8	4	2010-03-04 18:52:47[/COLOR][/B]
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 

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

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

50000 rows is insignificant. make sure your timestamp columns are indexed though.

5

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

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?


(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`