Data Correction for - Efficient Select statement need

From: Colin Woods <newsgroupcw_at_hotmail.com>
Date: Fri, 22 May 1998 15:41:52 +0100
Message-ID: <35658EB0.66D3@hotmail.com>

Folks

thanks to those of you who replied with good solutions, but due a mistake on my behalf, the solutions will not produce the correct result.

I have modified the data correctly below.

| A | B | C | D | <-- Column names.

| 1 | x | y | t | <-- Column data.
| 2 | x | y | z |
| 3 | x | y | z |
| 4 | x | y | z |
| 6 | x | y | z |
| 7 | a | b | c |
| 8 | a | b | c |
| 9 | x | b | c |

results:

| Start A | Finish A | B | C | D |

| 1 | 1 | x | y | t |
| 2 | 4 | x | y | z |
| 6 | 6 | x | y | z |
| 7 | 8 | a | b | c |
| 9 | 9 | x | b | c |

What I need to do is SELECT everything from table T, but only SELECT one column for records that have consecutive values of A and equal values of B, C and D, and show the start and finish values of A as well as the values of B, C and D.

the problem with the min and max solution is that if table T above also contained the column

| A | B | C | D |

|25 | x | y | z |

the then results would be

| Start A | Finish A | B | C | D |

| 1 | 1 | x | y | t |
| 2 | 27 | x | y | z |
| 6 | 6 | x | y | z |
| 7 | 8 | a | b | c |
| 9 | 9 | x | b | c |

instead of

| Start A | Finish A | B | C | D |

| 1 | 1 | x | y | t |
| 2 | 4 | x | y | z |
| 6 | 6 | x | y | z |
| 7 | 8 | a | b | c |
| 9 | 9 | x | b | c |
| 27 | 27 | x | y | z |

here is a create statement to set up the table T

create table T
(A number, B varchar2(2), C varchar2(3), D varchar(2));

insert into T values (1, 'x', 'y', 't');
insert into T values (2, 'x', 'y', 'z');
insert into T values (3, 'x', 'y', 'z');
insert into T values (4, 'x', 'y', 'z');
insert into T values (6, 'x', 'y', 'z');
insert into T values (7, 'a', 'b', 'c');
insert into T values (8, 'a', 'b', 'c');
insert into T values (9, 'x', 'b', 'c');

Thank you

Colin Woods. Received on Fri May 22 1998 - 09:41:52 CDT