13 Replies Latest reply on May 30, 2016 7:00 PM by beverly

    How to retrieve the 2nd largest value?

    veritasac

      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.

        • 1. Re: How to retrieve the 2nd largest value?
          CamelCase_data

          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 )

          )

          1 of 1 people found this helpful
          • 2. Re: How to retrieve the 2nd largest value?
            veritasac

            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.

            • 3. Re: How to retrieve the 2nd largest value?
              electon

              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.

              1 of 1 people found this helpful
              • 4. Re: How to retrieve the 2nd largest value?
                veritasac

                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?

                • 5. Re: How to retrieve the 2nd largest value?
                  electon

                  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.

                  1 of 1 people found this helpful
                  • 6. Re: How to retrieve the 2nd largest value?
                    veritasac

                    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.

                    • 7. Re: How to retrieve the 2nd largest value?
                      electon

                      That's for all records in the table.

                      If you want to calculate second highest only for current record then you can use the Case ( ) function in the same manner.

                       

                      Case (

                           col1 >= col2 and col1 <= col3 ; col1 ;

                           col2 >= col1 and col2 <= col3 ; col2 ;

                           col3

                      )

                      1 of 1 people found this helpful
                      • 8. Re: How to retrieve the 2nd largest value?
                        electon

                        Unfortunately, there's no SecondLargest ( field1 ; filed 2 ; ... ) function similar to Max ( )

                         

                        You make up what you need using existing ones and Case ( ) is the best fit for this one.

                         

                        HTH

                        1 of 1 people found this helpful
                        • 9. Re: How to retrieve the 2nd largest value?
                          erolst

                          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 …

                          1 of 1 people found this helpful
                          • 10. Re: How to retrieve the 2nd largest value?
                            veritasac

                            @Electron - This solution worked! Thanks so much.

                             

                            And a big thanks to @David and @erolst your help made me understand FM even more - steep learning curve!

                            • 11. Re: How to retrieve the 2nd largest value?
                              DrewTenenholz

                              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

                              )

                              • 12. Re: How to retrieve the 2nd largest value?
                                user19752

                                For 3 numbers, you can get 2nd (it is middle) as

                                Sum ( f1 ; f2 ; f3 ) - Max ( f1 ; f2 ; f3 ) - Min ( f1 ; f2 ; f3 )

                                 

                                Substituting method removes all max values, so the result is different from Excel LARGE() function if top and 2nd is the same value. (I don't know this is bad or good for you)

                                • 13. Re: How to retrieve the 2nd largest value?
                                  beverly

                                  Yes, this works if there are duplicate values.

                                  beverly