Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    3

    Question Unanswered: need help getting query right

    Hi,
    I'm trying to run a query on a multi-record table that will tell me the two highest numeric values within the set of numeric values associated with each record ID. I've put together a set query that almost works. It is a three line query as follows:
    File name Record ID Numeric Values
    Set example ID _a
    Set example ID <MAX _a,_b
    Check Check >=Max _b

    The problem is that I have duplicate maximum values in some records and this query obviously ignores these duplicates and finds the next lowest numeric value. for set b.
    Also, for some reason, I sometimes get only one value returned rather that two for a given record ID. I have no idea why this is happening.
    Can anyone help me work out these two problems?
    Thanks!!

  2. #2
    Join Date
    Dec 2007
    Posts
    366
    if you can have two or more records with the same value, you might want to approach this in a completely different manner..

    first, I'd do a "calc count all" of that field value, per ID, and sort the results by "count".. then I'd scan thru the table, eliminating the excess records..
    --
    Steven Green - Myrtle Beach, South Carolina USA

    http://www.OasisTradingPost.com

    Oasis Trading Post
    - Collectibles and Memorabilia
    - Vintage Lego Sets and Supplies
    - and Paradox Support, too

  3. #3
    Join Date
    Nov 2008
    Posts
    3

    More information about query

    Actually, I don't want to eliminate any of the duplicate values. I'd like the query to simply tell me what the two maximum values associated with a record ID are - whether they are ties or not. If the two maximum values are 26.25 and 26.25, that's fine.
    The problem is that the MAX operator seems to ignore a tied maximum value and look for the next lowest value in the groupo.

  4. #4
    Join Date
    Dec 2007
    Posts
    366
    If the two maximum values are 26.25 and 26.25, that's fine

    exactly.. but I don't think you can do that with a generic SQL query.. you're asking it to do the max, do the count, and evaluate the results.. there's a different process used for that.. it's called "a programmer" (grin)
    --
    Steven Green - Myrtle Beach, South Carolina USA

    http://www.OasisTradingPost.com

    Oasis Trading Post
    - Collectibles and Memorabilia
    - Vintage Lego Sets and Supplies
    - and Paradox Support, too

  5. #5
    Join Date
    Nov 2008
    Posts
    3
    Golly - that's just so super helpful.

  6. #6
    Join Date
    Dec 2007
    Posts
    366
    Golly - that's just so super helpful.

    well, without knowing what platform you're using, and what your level of knowledge is, there's not much more that I can do than say "you can't do it that way".. I don't know what other options you might have..
    --
    Steven Green - Myrtle Beach, South Carolina USA

    http://www.OasisTradingPost.com

    Oasis Trading Post
    - Collectibles and Memorabilia
    - Vintage Lego Sets and Supplies
    - and Paradox Support, too

Posting Permissions

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