Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Unanswered: SELECT only the newest, but based on revised letters

    Ok here's a big one.

    First I'm a big NEWBIE, so it'd be great if you can provide a little explanation as to how this should be done...

    Here's what I want to do, but have no idea how to approach it.

    I have a table with Quotes (table Quote) in them (for a Sale's Team). Each has a quote number (qtQN) and this number is sequencial but sometimes revised where a letter is added at the end. Like so:

    qtQN__________Date
    ---------------------------
    111q0001--------02/01/04
    111q0002--------02/02/04
    111q0002A------02/03/04
    111q0002B -----02/04/04
    222q0005--------01/15/04
    etc... ------------- etc...

    The first 3 digit are company codes and pretty unimportant to this problem. As you can see, 111q0002 has three versions. A, B, and no letter. The most up to date is B. So in this list I want to list only the most up to date quotes. So the resulting list would be:

    111q0001
    111q0002B
    222q0005

    Get it? Good, cuz I have no idea how to query that... any help at all is appreciated!

    I'm using MS SQL Server 2k and scripting with ASP 3.0

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, I won't go into what poor practice this is, storing multiple types of information in a single field, 'cause you probably have no control over it, so....

    select max(qtQN) as Maxqtqn from YourTable group by left(qtQN, 8)

    ...should give you the list you want.
    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 2004
    Posts
    7

    Talking

    Well that worked! Thanks!!!

    But I'm interested in knowing more by what you meant here:

    Originally posted by blindman
    Well, I won't go into what poor practice this is, storing multiple types of information in a single field,
    Which multiple types? the date is a seperate column, but I listed it here in case it would be helpful to show that the ones that are the most recent. The company code is also listed as another column, but the QN needs to contain it for our humans to quickly know which company that quote was to... but still if I haven't hit it, what would have been a better way to design this?

    Thanks for your input!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "111q0001B" appears to contain more than one piece of information. A general rule of database design is that a field should contain only one piece of information. In a good design this would be divided into four separate fields: 111, q, 0001, and B. I would bet, however, that this is part of your business process, not your database design, and so you're probably stuck with it.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Feb 2004
    Posts
    5

    Re: SELECT only the newest, but based on revised letters

    Code:
    Select q1.*
      from ( Select Max( qtQN) as currentQN
            from Quotes
            group by Substring( qtQN, 1,8) ) SUB1
        inner join Quotes Q1
            on q1.qtQN = sub1.currentQN

  6. #6
    Join Date
    Jan 2004
    Posts
    7
    Actually, the letter "Revisions" (Rev) CAN and SHOULD be another column in the DB... I was talking to a friend and she suggest that would be better (also a status column of dead and alive quote as well...) so thanks blindman! I'll see if I can separate the company code too (it already exists as a column, but I build the qtQN by combining all the componants together, I'm sure there's a better way, but I'm an anthropologist by trade... so this is only my day job!)

    thanks Clay for the suggestion... and now back to redesigning the schema a bit!


    Originally posted by blindman
    "111q0001B" appears to contain more than one piece of information. A general rule of database design is that a field should contain only one piece of information. In a good design this would be divided into four separate fields: 111, q, 0001, and B. I would bet, however, that this is part of your business process, not your database design, and so you're probably stuck with it.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you can store the values separately but need them concatenated for the business users, consider adding a calculated field to your table that consists of the concatenated values. That way you don't have to worry about maintaining it.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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