Find the 2nd Highest Value

[DanBrill]

Hey there,

If I wanted to find the highest value within a relationship I'd use

max(relationship::field)

How do I find the 2nd highest value?

Thanks,

Dan

[Vaughan]

Omit the highest valued records and use the Max() function again? Just guessing here...

[RussBaker]

Or you could do a find in the child file, using the relationship key as the "find" field, sort them in decreasing order and then go to record number 2.

[Pupiweb]

It's easily scripted, it's more cumbersome if you want to have a calc field

You might do it sorting the relationship, grabbing the related values using a relational value list and the ValueListItems function and finally using the text parsing function to get the second item in the list

See this tip file on the matter:

http://www.buliga.it/TipFiles/Ultimi%20due%20RR.sit

http://www.buliga.it/TipFiles/Ultimi%20due%20RR.zip

[Ugo DI LUCA]

Hi,

Category field

Price

MaxPrice by category = Max(selfjoinOnCategory::Price)

Intermediate Calc = Case(Price < MaxPrice by category,Price, 0)

MaxSecondHighest Price = Max(SelfjoinOnCategory::Intermediate Calc)

Intermediate2 = Case(Price<MaxSecondHighest Price, Price,0)

MaxThirdHighest Price = Max(SelfjoinOnCategory::Intermediate2)

...

[DanBrill]

Thanks everyone,

These are great ideas. Ugo's solution does the trick and avoids using a script, so I'm declaring it the winner. ;-)

I hope I can get to the point where I'm answering half as many questions as I'm asking.

Cheers,

Dan

[Ugo DI LUCA]

Hi Dan,

Actually, after re-reading all the answers, I think Vaughan had it the same way .

Omit the highest valued records and use the Max() function again? Just guessing here...

Just guessing here ?

I want to have Vaughan kids!!!

[DanBrill]

Oh yeah, I guess so. Thanks, Vaughn. It makes more sense now that I've walked through it.

Unfortunately, after I got it up and running, I realized that that wasn't quite what I was after. It's more subtle than that, and I'm not sure I can even really describe the problem. I'm still working on wrapping my head around it.

But thanks for the help. Always good to learn a new technique.

Dan

[Vaughan]

Ugo wrote: "I want to have Vaughan kids!!!"

ROTFLMAO!

[Ugo DI LUCA]

I'm still clueless with this "children"/ "kid" problem. And I'm now clueless with ROTFLMAO....

Too many secrets

[Lee Smith]

Rolling on the floor laughing my a.. off

[Ugo DI LUCA]

Hi Lee,

One secret is solved now...

[DanBrill]

Hey Guys,

Now that everyone has ROTF and LTAs off, I figured out what my real question was. (Must be all that laughing relieved some stress and helped me to clarify my thought process.) :-)

I didn't just want to find the second highest value in a relationship, but the highest value in a relationship that is less than the current value.

For example...

A 1

B 2

C 3

D 4

E 5

So for record B, the highest value that is less than B's value (i.e., 2) is 1. For record C it is 2. For record D it is 3, and so forth.

Any thoughts on how to find this one? I'm thinking that I'll have to rely on a script for this. Yet in the back of my head I still think I can do it with a calculation. But my logic centers must be out of whack, 'cause I can't get this.

Thanks,

Dan

[Ugo DI LUCA]

Hi Dan,

Scripted method I think.

Set fied g_record_ID (record_ID)

Go to related record (show all)

Sort on your number field descending

Loop

Go to next record

End Loop when Record_ID = g_record_ID

Go to previous record

Omit

Show omitted.

[DanBrill]

Thanks Ugo,

I've gone with the script method. The actual situation was quite a bit more complex than I described, so the script got pretty convoluted. But your's was a good starting point. It seems cumbersome -- at least it was to write -- but it works and it seems pretty quick. We'll see once I get the db full of data.

Cheers,

Dan

[Ugo DI LUCA]

Hi Dan,

This scripting method would lead you to another layout, isolating (omitting...) one record...Not really dynamic....

Depending on your file structure and interface needs, you should take a look at "fixed portals" at http://www.fmfiles.com or even DJ's renumbering portal in the sample section.

Scripting the find using these solutions, would offer you a cleaner way to view at the fly within the current layout (you surely have some room left) the record you are looking for, even highlightting it.