MySQL Forums
Forum List  »  Newbie

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

Options: ReplyQuote


Subject
Written By
Posted
Re: I want to write a user-defined Aggregate Function
September 11, 2012 01:29AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.