In Excel the formula is: =LARGE(E4:E9;2)

I can do the maximum formula Max(field{;field...})

But in FM how do I achieve returning the 2nd max value like the Excel formula?

Thanks for any help.

In Excel the formula is: =LARGE(E4:E9;2)

I can do the maximum formula Max(field{;field...})

But in FM how do I achieve returning the 2nd max value like the Excel formula?

Thanks for any help.

thank you so much for your help and taking the time to respond. I will attempt what you typed.

I am completely new to FM and had hoped that the equation would be a little easier without the SQL. Do you know if this is possible because I have the excel file imported into FM and have a layout that it publishing certain fields, I just want to compare 3 values across a row of data and return the 2nd highest.

Thanks again.

- 1 person found this helpful
You can also do everything inside one query.

Let (

sql = "SELECT MAX ( col ) FROM table WHERE col < ( SELECT MAX ( col ) FROM table)";

ExecuteSQL ( sql ; "" ; "" )

)

substitute col for your field name and table for your table name.

It's by far the easiest to achieve with eSQL

Bare in mind you need at least two records to compare against otherwise the result will be empty.

You can test for this before you execute. Get ( FoundCount ) will give you number of records.

Thanks Electron but how does this change if I'm viewing/publishing data for a single record (row) and I want to compare 3 items for the same record (3 cells in the same row) and then I want to publish the 2nd biggest (or the third)?

The layout I've created is only using a single table to feed it so I'm wondering if I have to do an sql query or if there is something like Excel's =LARGE(E4:E9;2) which looks for the 2nd biggest out of the items referenced?

- 1 person found this helpful
Well, that's a bit more complicated.

You want to compare the values across 3 columns and get the second largest value.

Let (

sql = "SELECT MAX (

CASE

WHEN col1 >= col2 AND col1 <= col3 THEN col1

WHEN col2 >= col1 AND col2 <= col3 THEN col2

ELSE col3

END )

FROM MyTable" ;

ExecuteSQL ( sql ; "" ; "" )

)

The CASE statement compares the columns and returns the second highest per row then it's aggregated with MAX.

Thanks again for your help.

Just for final clarification, because the software is not online and I'm using a single table and layout in FMP14 do I absolutely need SQL queries?

For example, on another part of the layout I'm returning the max value by using the simple code below:

**Max ( scoreone; scoretwo; scorethree; scorefour )**This is simply by creating a field in the table that is a calculation of other fields (same row). The above code returns the maximum.

If I'm trying to return the second max, do I have to use SQL queries or CASE? I really thought this would be much simpler given that it's not an online app nor is it retrieving data across tables or multiple rows.

Thanks again.

- 1 person found this helpful
veritasac wrote:

If I'm trying to return the second max, do I have to use SQL queries or CASE? I really thought this would be much simpler given that it's not an online app nor is it retrieving data across tables or multiple rows.

Thanks again.

Actually, it's much easier doing statistics on a single field across many records than vice versa.

You could try:

Let ( [

theMax = Max ( f1 ; f2 ; f3 ) ; // any number of fields

theList = List ( f1 ; f2 ; f3 ) & ¶ ; // the same fields

newList = Substitute ( theList ; theMax & ¶ ; "" ) ;

vc = ValueCount ( newList )

] ;

Case (

vc = 1 ; newList ;

vc > 1 ; Evaluate ( "Max (" & Substitute ( newList ; ¶ ; ";" ) & ")" )

)

)

Probably overkill …

veritasac --

Here's a more old-fashioned way to get the same result, using only FileMaker functions from the functions list. No SQL involved. It should succeed and fail in the same ways that the SQL answer does. I did some preliminary testing, and you'll see some variable coercion in it which helps FileMaker continue to consider the values as numbers rather than text.

If I were using this myself, I'd probably include a custom function for removing values from lists of values, but I didn't want to over-complicate the answer. That's why you see the extra carriage returns/pilcrow/¶ character in the calculation (which Jive will inevitably munge....)

You can even skip the first three declarations, they are one way to test the function in the data viewer without actually having to create a file at all.

As you can see from both answers, FileMaker is not Excel, and the kinds of manipulations FileMaker can do are different, especially if you want to get results for 'the next column over' or 'the next row down' in the way Excel, well, excels at.

-- Drew Tenenholz

Let ( [

field1= 23 // use your number field name here

; field2= 11 // use your number field name here

; field3= 21 // use your number field name here

; fullList= List ( field1 ; field2 ; field3 ) &¶

; maxValue= Max ( field1 ; field2 ; field3 )

; listWithoutTop= Substitute ( fullList ; maxValue &¶ ; "" )

; penultimateMax = Max ( GetAsNumber ( GetValue ( listWithoutTop ; 1 ) ) ; GetAsNumber ( GetValue ( listWithoutTop ; 2 ) ) )

];

penultimateMax

)

There are many ways; with ExecuteSQL you can e.g. use:

Let (

_Max =

ExecuteSQL ( " SELECT MAX ( MyNumberField ) FROM MyDataTable " ; "" ; "" )

;

ExecuteSQL ( " SELECT MAX ( MyNumberField ) FROM MyDataTable WHERE MyNumberField < ? " ; "" ; "" ; _Max )

)

or

Let (

_DescendingList =

ExecuteSQL ( " SELECT MyNumberField FROM MyDataTable GROUP BY MyNumberField ORDER BY MyNumberField DESC " ; "" ; "" )

;

GetValue ( _DescendingList ; 2 )

)