Re: Efficient SELECT statement needed

This should be faster

SELECT MIN(A) "start A", MAX(A) "finish A",B,C,D INTO V_START, V_FINISH, V_B, V_C, V_D
FROM PROBLEM
GROUP BY B,C,D
ORDER BY 1; Greetz,

Martin

Colin Woods <newsgroupcw_at_hotmail.com> schrijfbewerkingen: > Folks
>
> I need a efficient select statement as I need to process up 60,000
> records from a single table.
>
> Let say we have table T, that contains the following columns and data
>
> -----------------
> | 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 |
> -----------------
>
> 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 above table would return the following result.
>
> -----------------------------------
> | Start A | Finish A | B | C | D |
> -----------------------------------
> | 1 | 1 | x | y | t |
> | 2 | 6 | x | y | t |
> | 7 | 8 | a | b | c |
> | 9 | 9 | x | b | c |
> -----------------------------------
>
>
> We already have a solution to this problem using PL/SQL but it is much
> to slow when dealing with a large number of records. This solution
> selects all records from table T into a cursor. Each record was then
> processed one by one. When a 'run' is detected the finish A value is
> stored in an extra column E of the first record in the run, with all
> further records being deleted.
>
> If anyone can provide a way to approach solving this problem it would be
> much appreciated.
>
> Thank you
>
> Colin Woods.
Received on Fri May 22 1998 - 08:27:03 CDT