Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178

    Question Unanswered: report on non-sequential values

    I need to create a report that will show the missing values from a sequential field (like an auto number for instance, but not). The values won't be numeric. they might be something like ABC001, ABC002, ABC003, etc

    how would I do this?
    thanks in advance

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Code:
            ID
    ----------
             1
             2
             4
             6
             7
             8
             9
            13
    select v.id-(v.DIFF-1) As "A", v.id-1 As "B"
    from
    (select t.id, t.id - (select max(id) from table where id < t.id) As "DIFF"
    from table t) v
    where v.diff > 1
    Code:
             A          B
    ---------- ----------
             3          3
             5          5
            10         12
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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