View With Grouping

Hi,

I am attempting to create a view that will rollup or group like, consecutive
data. I have created a view using unions, but the statement is extremely
slow.

Here is example data

Colour Table:

Colour_id hole_number depth_from depth_to colour
1 1 0 2
red
2 1 2 6
red
3 1 6 8
blue
4 1 8 10
blue
5 1 12 14
blue
6 1 14 16
red
7 2 0 5
orange
8 2 5 10
orange
9 2 10 15
red


Using this example data, we need to create groups of all consecutive data
with the same colour for the same hole.

For example, my view needs to produce the following output where depth_from
is the smallest depth_from in the group and depth_to is the largest depth_to
in the group

hole_number depth_from depth_to colour
1 0 6 red
1 6 14 blue
1 14 16 red
2 0 10 orange
2 10 15 red

The groups can contain gaps, but no overlaps. The data in each group must
be consecutive and is grouped by the colour column.

Here is my existing statement. The first select brings back all groups
containing multiple rows, the second select brings back all groups that
aren't multiples and aren't the first or last row, the third select brings
back the last row as a group if it doesn't fall within a group already, and
the last select returns the first row as a group if it doesn't fall within a
group already.


select min(colour.depth_from) as min_depth_from,

max_colour.depth_to as max_depth_to,

colour.colour,

colour.hole_number as hole

from colour

join colour as max_colour

on max_colour.hole_number = colour.hole_number

AND max_colour.colour=colour.colour

AND max_colour.depth_from > colour.depth_from

AND max_colour.depth_to > colour.depth_to

AND (select count(colour_id)

from colour as mid

where mid.depth_from < max_colour.depth_from

AND mid.depth_from > hole_colour.depth_from

AND mid.depth_to < max_colour.depth_to

AND mid.depth_to > colour.depth_to

AND mid.hole_number = colour.hole_number

AND mid.colour= colour.colour) >= 0

AND (select count(colour_id)

from colour as mid

where mid.depth_from < max_colour.depth_from

AND mid.depth_from > colour.depth_from

AND mid.depth_to < max_colour.depth_to

AND mid.depth_to > colour.depth_to

AND mid.hole_number = colour.hole_number

AND mid.colour<> colour.colour) = 0

AND max_colour.depth_to = (select max(depth_to)

from colour. as maximum_colour

where maximum_colour.depth_to >
colour.depth_to

AND maximum_colour.depth_from >
colour.depth_from

AND maximum_colour.hole_number =
colour.hole_number

AND maximum_colour.colour= colour.colour

AND (select count(colour_id)

from colour as
mid

where
mid.depth_from <= maximum_colour.depth_from

AND
mid.depth_from > colour.depth_from

AND mid.depth_to
<= colour.depth_to

AND mid.depth_to[color=blue]
> colour.depth_to[/color]

AND
mid.hole_number = colour.hole_number

AND mid.colour<>
colour.colour) = 0)



group by colour.hole_number, colour.colour, max_colour.depth_to,
max_colour.depth_from



UNION

select min(colour.depth_from)as min_depth_from,

max(colour.depth_to) as max_depth_to,

colour.rock_type_code,

colour.hole_number as hole

from colour

join colour as next_colour

on next_colour.depth_from = colour.depth_to

and colour.hole_number = colour.hole_number

AND next_colour.colour <> colour.colour

JOIN colour as prev_colour

on prev_colour.depth_to = colour.depth_from

and prev_colour.hole_number = colour.hole_number

AND prev_colour.colour <> colour.colour

group by colour.hole_number, colour.colour, colour.depth_from,
colour.depth_to



UNION

select min(colour.depth_from)as min_depth_from,

max(colour.depth_to) as max_depth_to,

colour.colour,

colour.hole_number as hole

from colour

JOIN colour as prev_colour

ON prev_colour.depth_to = colour.depth_from

and prev_colour.hole_number = colour.hole_number

AND prev_colour.colour<> colour.colour

and colour.depth_to = (select max(colour.depth_to)

from colour

where colour.hole_number =
prev_colour.hole_number)

group by colour.hole_number, colour.colour, colour.depth_from,
colour.depth_to



UNION

select min(colour.depth_from)as min_depth_from,

max(colour.depth_to) as max_depth_to,

colour.colour,

colour.hole_number as hole

from colour

JOIN colour as next_colour

ON next_colour.depth_from = colour.depth_to

and next_colour.hole_number = colour.hole_number

AND next_colour.colour<> colour.colour

and colour.depth_from = (select min(colour.depth_from)

from colour

where colour.hole_number =
next_colour.hole_number)

group by colour.hole_number, colour.colour, colour.depth_from,
colour.depth_to







Any help to speed this statement up or to re-write this statement so that it
is quicker would be appreciated.


Thanks,

Karen