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!!

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..

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.

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)

Golly - that's just so super helpful.

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..