Re: I want to write a user-defined Aggregate Function
Posted by:
Adnan Raza
Date: September 11, 2012 01:29AM
Hi Laptop,
Please have a look at following , thanks.
A)
drop table if exists tmp;
create table tmp (serial int primary key, special_check varchar(50));
insert into tmp
values
(1,'RL1093701'),
(2,'RL1093702'),
(10,'RL1093989'),
(20,'RL1097601'),
(21,'RL1097602'),
(22,'RL1097603'),
(23,'RL1099596'),
(24,'RL1099597'),
(25,'RL1099598'),
(26,'RL1099599'),
(27,'RL1099600'),
(28,'RL1099601'),
(29,'RL1099602');
output required;
Start End
RL1093701 RL1093702
RL1093989 RL1093989
RL1097601 RL1097603
;
Please do ensure that you apply a check on all LEFT JOINs that they include only following records in its processing instead of processing on all millions of records
('RL1093701','RL1093702' , 'RL1093989', 'RL1097601', 'RL1097602', 'RL1097603')
because I cant give you insert statements of millions of records.
MYSQL VERSION = 5.0.77-community-nt
I was trying to implement it in following way,
;
select
a.serial Start
, MIN(c.serial) End
from
tmp a
join tmp ac on ac.serial in (a.serial)
LEFT
JOIN tmp b
ON b.serial + 1 = a.serial and b.serial in (ac.serial)
LEFT
JOIN tmp c
ON c.serial >= a.serial and c.serial in (ac.serial)
LEFT
JOIN tmp d
ON d.serial - 1 = c.serial and d.serial in (ac.serial)
WHERE
b.serial IS NULL
AND c.serial IS NOT NULL
AND d.serial IS NULL
and a.special_check in ('RL1093701','RL1093702' , 'RL1093989', 'RL1097601', 'RL1097602', 'RL1097603');
Subject
Written By
Posted
September 06, 2012 01:10AM
September 06, 2012 04:10AM
September 06, 2012 05:41AM
September 06, 2012 07:00AM
September 06, 2012 10:54PM
September 11, 2012 07:58AM
September 11, 2012 10:23PM
September 07, 2012 01:02AM
September 07, 2012 01:24AM
September 08, 2012 05:39PM
September 10, 2012 07:24AM
September 10, 2012 07:31AM
September 10, 2012 07:57AM
September 10, 2012 08:01AM
September 10, 2012 08:11AM
September 10, 2012 08:57AM
September 10, 2012 10:26AM
Re: I want to write a user-defined Aggregate Function
September 11, 2012 01:29AM
September 11, 2012 08:10AM
September 11, 2012 10:21PM