Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2007
    Posts
    19

    Unanswered: Can SQL find duplicates within a "range" of numbers?

    We have an SQL server and keep records of paper numbers used. Our system pulls from a table of available numbers.

    The fields are: LOW, HIGH, NEXT, CLOSED.

    LOW = the low number in the paper batch.
    HIGH = the high number in the paper batch.
    NEXT = the next number. Auto-increments when we print.
    CLOSED = Batch is closed when NEXT equals HIGH. No more paper avail.

    We want to be able to check if there are any duplicates when adding a new row. If we have a LOW of 10 and HIGH of 50 with an ENDING of 30, and we want to add a new row, what SQL statement can be written to check if there are no numbers BETWEEN 10 and 50 for LOW or HIGH? There are no physical rows to show these numbers.

    I know how to check if 10 or 50 exists, but i need to know how to get every number in between and check them.

    Thanks,
    Eric

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sample data and desired results, please.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2007
    Posts
    19
    Data:

    LOCATION BEG# END# NEXT# OPEN?
    CENTRAL 23515518 23515525 23515525 N
    CENTRAL 23515620 23515635 23515635 N
    CENTRAL 23515670 23515676 23515676 N
    CENTRAL 23515714 23515716 23515716 N
    CENTRAL 23515849 23515853 23515853 N
    CENTRAL 23516034 23516046 23516046 N
    CENTRAL 23516289 23516294 23516294 N
    CENTRAL 23516555 23516562 23516562 N
    CENTRAL 23516646 23516650 23516651 N

    Output from query: I just want to know if a row exists. So, something like

    DIM DUPLICATE
    DUPLICATE = (Select ROWID from TABLE where BEG# "is already used")

    Then I would say if DUPLICATE <> 0 then msgbox "You have entered number which has already been used"

    The part where I am stuck is the "is already used" in the select statement.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    SELECT Beg FROM Table As 't' WHERE Beg = (SELECT Beg FROM Table WHERE Beg = t.Beg)

    HHmm, that doesn't look right... Someone correct me please
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You're mixing terminology. So my first question is whether LOW = Begin and HIGH = End? Also, you are using "BEG#" in multiple contexts.

    I believe you just want to do regular interval overlap tests, right? The following query assumes that "start" is the begin of the interval that should not overlap with any other interval in the table, and "stop" is the end of that interval. If you are only interested in a single value, you have a point interval, i.e. start = stop. The assumption is that we have always start <= stop and begin <= end.
    Code:
    SELECT COUNT(*)
    FROM   yourTable AS n
    WHERE  n.begin <= stop AND n.end >= start
    If the query returns a value largen than 0, you have an overlap. Alternatively, you can use "SELECT *" or whatever.

    Note: there is no ROWID in your table and standardized SQL doesn't define such a constructor. That's why you can't use this in general - only on your specific DBMS.

    For point intervals, you could actually write this as well:
    Code:
    SELECT ...
    FROM   yourTable AS n
    WHERE point BETWEEN n.beg AND n.end
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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