Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Aug 2003
    Posts
    11

    Question Unanswered: Really need help with this tough query

    Hello all. What I want to do has left me stumped. I will try to articulate everything, but please reply if I have omitted some details.

    My goal is to effectively choose the next sequencial contract number as follows: there are two tables, each with a contract_number field, whose format is as follows:
    ANNNA-NNNN, where A represents a single alphabetic character, and each N represents an integer.
    What I need done is to chose the largest value occupying the last four digits of a contract number.

    An example would clarify:

    table1
    C600D-1234
    C500E-1231

    table2
    A400F-1235
    G300I-1222

    My query should return: A400F-1235 (or just 1235) since the last four digits are greatest in this instance.
    I have a feeling that I will use the functions CAST, MAX, and SUBSTRING, but this is just too complicate for me. To make matters worse, there will be some obsolete ill-formatted numbers that should be ignored in case they do not conform to the format.

    Even the smallest bit of advice could prove priceless.
    Thanks!

  2. #2
    Join Date
    Aug 2003
    Posts
    11

    Re: Really need help with this tough query

    To make this problem a whole lot more manageable, forget the issue about there being two table; I can take care of that with my server-side code. So basically, I just need the maximum integer value retrieved from a column containing both numbers and letters, as described in the previous post. Again, however, I need a way to cope with (ignore) poorly-formatted contract numbers.

    Thanks!


    Originally posted by ujohnc00
    Hello all. What I want to do has left me stumped. I will try to articulate everything, but please reply if I have omitted some details.

    My goal is to effectively choose the next sequencial contract number as follows: there are two tables, each with a contract_number field, whose format is as follows:
    ANNNA-NNNN, where A represents a single alphabetic character, and each N represents an integer.
    What I need done is to chose the largest value occupying the last four digits of a contract number.

    An example would clarify:

    table1
    C600D-1234
    C500E-1231

    table2
    A400F-1235
    G300I-1222

    My query should return: A400F-1235 (or just 1235) since the last four digits are greatest in this instance.
    I have a feeling that I will use the functions CAST, MAX, and SUBSTRING, but this is just too complicate for me. To make matters worse, there will be some obsolete ill-formatted numbers that should be ignored in case they do not conform to the format.

    Even the smallest bit of advice could prove priceless.
    Thanks!

  3. #3
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Try this

    create table foo (test varchar(30))

    insert into foo select 'abcd-1234' union all select 'dfrg-1235' union all select 'adgf-9999'

    select test from foo where right(test,4) in (select max (right(test,4)) from foo)

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think you would get faster performance using the = operator rather than the "in" operator:

    where right(test,4) = (select max (right(test,4)) from foo)


    ...but unless the query is very large the difference is probably negligable.

    Another minor point; you have the option of running your select clause from the union query directly, without using a temporary table if you want.

    blindman

  5. #5
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Oops, absolutely right, you want to use the '=' not 'in'

    Thanks
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  6. #6
    Join Date
    Aug 2003
    Posts
    111
    Originally posted by rhigdon

    insert into foo select 'abcd-1234' union all select 'dfrg-1235' union all select 'adgf-9999'

    HTH
    hi, i am new to all these db stuff
    but wat does the above statement do

    i don't get the
    select 'abcd-1234' union all select 'dfrg-1235' union all select 'adgf-9999'
    part

    thanx

  7. #7
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Just a way to insert rows instead of saying

    insert into table select 'blah'
    insert into table select 'blah'
    insert into table select 'blah'

    Using the union all statement allows you to write one insert that accomodates multiple inserts.

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  8. #8
    Join Date
    Aug 2003
    Posts
    111
    Originally posted by rhigdon
    Just a way to insert rows instead of saying

    insert into table select 'blah'
    insert into table select 'blah'
    insert into table select 'blah'

    Using the union all statement allows you to write one insert that accomodates multiple inserts.

    HTH
    arhh... thanx,

    is there any good resources that you gurus out would recommend me to read, either books or online resources

    cheers
    j

  9. #9
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    BOL - http://microsoft.com/sql/techinfo/pr...2000/books.asp

    Inside SQL Server - Kalen Delaney

    Data and databases - Joe Celko

    Guru's guide to transact SQL - Ken Henderson

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  10. #10
    Join Date
    Aug 2003
    Posts
    11
    thank you all for your replies.

    i think your suggestions will work for my situation, with one exception: some of the sequence numbers are poorly-formatted, and in that case, the "max (right(test,4))" may cause an error, or produce undesriable results. basically, if "right(test,4)" is not an integer, i don't want to deal with that record. any ideas of how to accomplish this?

    also, blindman wrote:
    "Another minor point; you have the option of running your select clause from the union query directly, without using a temporary table if you want."

    Can you show the sql code for this (I can't picture what you mean)?

    Thank you infinitely!

  11. #11
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    You can use the isnumeric function.

    Not tested but something like this:

    if
    isnumeric(right(test,4)) = 1
    then
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This illustrates how to use UNION to combine both tables into a subquery, for both your outer select and for determining the largest current value. Whether you use this, a temporary table, or a table variable is a matter of choice and the details of your project.

    This also shows how to use the ISNUMERIC function to screen out non-numeric bad data.

    select test
    from (select * from table1 Union select * from table2) foo
    where right(test,4) =
    (select max(right(test, 4))
    from (select * from table1 Union select * from table2) foo2
    where ISNUMERIC(right(test, 4))=1)


    blindman

  13. #13
    Join Date
    Aug 2003
    Posts
    11
    blindman and rhigdon...

    thanks infinitely for helping me out. i believe i now have exactly what i want thanks to you.

    one curiosity: when we write SELECT MAX(RIGHT(...)), why doesn't some sort of cast to an integer need to take place (or does sql server do this implicitly)?

    take care.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can use the Max and Min aggregate functions on non-numeric data, which is then evaluated according the sort-order of the character set. For numeric data stored as text, there is no difference in the result as long as all values have the same number of digits.
    Of course, you can't use statistical functions like Avg with non-numeric data!

    blindman

  15. #15
    Join Date
    Aug 2003
    Posts
    11
    I bet you thought this thread was dead... never!

    anyways, thanks for the continual help.

    as a slight modfication to my original question, imagine now that the formatting is in one of the following formats (where L's represent letters, and N's represent integers):

    L600-NNN-LLLL
    LL600-NNN-LLLL
    LLL600-NNN-LLLL
    LLLL600-NNN-LLLL

    the question remains the same: how can I find the largest NNN in a column whose contents usually follow the above formatting? how can i ignore those numbers that don't conform to the above formatting? the reason that this formatting poses a new problem for me is that i can't use the RIGHT or LEFT functions, and therefore don't know how to parse out the NNN values.

    thank you in advance for your time.
    cheers

Posting Permissions

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