Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    2

    Unanswered: Omitting consecutive records repeating the data in a given column

    I have a db2 table where a code represents personal letter-writing at a given address. The records will be ordered by the date of that letter-writing in another column. I seek a report that omits all subsequent *consecutive* records with the same code in the address column but includes any later return to that address with, again, omission of any subsequent consecutive records at that address.

    GROUP BY selects just one of the records at that address (or 2, for MIN and MAX may be applied to the date column). The report I envisage will have as many records as there are returns to the given address plus the first one.

    The result would be the space-time history of a famous author. I'd be most grateful for tips on how to reach the result.

  2. #2
    Join Date
    May 2002
    Location
    Pune, India
    Posts
    23
    Hi,
    I have not been able to get what exactly you mean by "subsequent *consecutive* records". It would be more clear if you could illustrate your problem with examples.
    Anyways what I am listing is kind of shot in semi-dark. Let's see what modifications you want in the resultset.
    CODE DATE1
    ---- ----------
    A1 10/10/2002
    A1 10/10/2002
    A2 10/10/2002
    A1 10/10/2002
    A1 11/10/2002
    A3 11/10/2002

    6 record(s) selected.


    C:\>db2 select distinct code,date1 from t12

    CODE DATE1
    ---- ----------
    A1 10/10/2002
    A2 10/10/2002
    A1 11/10/2002
    A3 11/10/2002

    4 record(s) selected.


    Regards
    Sumeet

  3. #3
    Join Date
    May 2002
    Posts
    2
    Sumeet,

    Thanks for attempting to solve my problem. In your example the date1 data should be different. Thus only the code column has repeating data for consecutive records in the order chosen for the current report. I have modified your example table to show what I mean and your example report to show what I'm trying to get:

    CODE DATE1
    ---- ----------
    A1 10/10/2002
    A1 12/10/2002
    A2 12/20/2002
    A1 01/10/2003
    A1 11/10/2003
    A3 12/10/2003

    6 record(s) selected.


    C:\>db2 select distinct code,date1 from t12

    CODE DATE1 [modified by Ken]
    ---- ----------
    A1 10/10/2002
    A2 12/20/2002
    A1 01/10/2003
    A3 12/10/2003

    4 record(s) selected.

    However, now the query you suggest won't produce this report. Instead, its report will reproduce the table. When I run select distinct code, min(date1) from t12 I get:

    CODE DATE1
    ---- ----------
    A1 10/10/2002
    A2 12/20/2002
    A3 12/10/2003

    but that omits the return to A2. I don't want it omitted.

    Further suggestions will be much appreciated.

    Ken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •