Re: Efficient SELECT statement needed

Colin,
  I am making a couple of assumptions here:

1. Column A is integer and your definition of consecutive means incrementing by one and no dups. (if there can be duplicated number in column A for the same columns B, C & D, it will take other handling)
2. PL*SQL is still the "best" way to handle this. A cursor will do most of the work.
3. There will "incomplete" runs that will not be touched.
4. I am assuming that value of column E is not known and not controlled.

Define a cursor to be:

   SELECT B || C || D test_col, MIN(A) Start_A, MAX(A) Finish_A,

         COUNT(A) Count_A
      FROM T
      GROUP BY B || C || D ;

Iterate over the cursor (using "x" as the record). IF x.Count_A = (x.Finish_A - x.Start_A) + 1 THEN
   UPDATE T
      SET E = x.Finish_A
   WHERE A = x.Start_A AND B || C || D = x.test_col ;

   DELETE FROM T
   WHERE A <> x.Start_A AND B || C || D = x.test_col ; END IF ; Performance may be improved by using indexes if possible. Also, if the value of column E is known and controlled, i.e. it is always null until processed, the select statement could filter on E.

I just saw your "correction post" and based on that criteria, the only way to do what you want is with PL*SQL. I.E. if the rows B, C & D may have multiple "runs" for the same value set, it does not seem possible to do this in an SQL statement.

HTH
   James

 In article <3565432C.58F7_at_hotmail.com>, newsgroupcw_at_hotmail.com wrote:

>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.

--

lorenzen_at_tiny.net             | Life is complex; it has
                              |   real and imaginary parts