Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    7

    Unanswered: find Minimum in range thats not in Table?

    Hi Folks,

    any help appreciated on this problem:
    I've got a Table with a comparable Datatype (inet on postgres).
    The values in the table have a minimum and a maximum value.
    Now, I've got to find the smallest value betweeen min. and max. that is NOT in the table.

    Example:
    Min=10, Max=20
    Entries: 10, 11, 12, 14, 18
    needed value: 13 (larger than Min., smallest value not in Table)
    I dont have pure Numbers to deal with, so

    I dont want to create an auxiliary table with all the possible values and do a SELECT ... WHERE NOT IN ... statement.

    thanks in advance!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you give some examples of what "inet" values are?

    kinda curious why you chose the oracle forum to post

    http://dbforums.com/f81/ is the postresql forum

    and what's wrong with an auxiliary table?


    rudy

  3. #3
    Join Date
    Sep 2003
    Posts
    7
    Originally posted by r937
    could you give some examples of what "inet" values are?

    kinda curious why you chose the oracle forum to post

    http://dbforums.com/f81/ is the postresql forum

    and what's wrong with an auxiliary table?
    rudy
    Sorry, but my browser says 'SQL and PL/SQL' Forum; I'd say this is a SQL problem - not a postgres.

    'inet' is an ip(v4)-address - this datatype is comparable but not incrementable.

    I don't think it's necessary to create a table with some hundred continuous values that can be expressed by two borders; perhaps there is no other solution than an auxiliary table but that would be a pity.

    thanks anyway, Z

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, you're right, there's a separate oracle forum, although this one should definitely have the "PL/SQL" taken off its name

    if you want an sql solution and not a postgresql solution, the only ones i'm familiar with are:

    -- NOT EXISTS
    -- NOT IN
    -- EXCEPT
    -- OUTER JOIN with test for no match

    each of these requires some way of specifying the set of things that aren't there

    you don't actually have to have an auxiliary table, though

    try joining the table to itself with a left outer join on a.inet = (b.inet - 1)

    or something


    rudy

  5. #5
    Join Date
    Sep 2003
    Posts
    7

    Red face

    Originally posted by r937
    ...
    try joining the table to itself with a left outer join on a.inet = (b.inet - 1)

    or something

    rudy
    darn, you were right from the beginning: As the 'inet'-Type cant be incremented or decremented a pure SQL solution seems even more unapplicable now. I think I have to do some postgres-specific hack...

    thanks,

    zaphod

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, but i didn't know i was right at the time!

    meanwhile, i had a look at the postgresql docs, and found this page, which (a) freaks me out, but (b) offers encouragement that there might be custom functions available in postgresql for working with that datatype

    rudy

Posting Permissions

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