ExecuteSQL distinct values from one field only

[CoachMan]

I'm trying to populate a virtual list table using ExecuteSQL, to show the most recently logged foods by the current user.  I need to show several fields (FoodID, food_name, portion, unit, calories, log_date, etc) but only want distinct values based on the FoodID field.  For any duplicates, I want to show the most recent one. 

For example if the data set is:

1, apple, 1, ounce, 10, 2020-06-10

1, apple, 2, ounce, 20, 2020-06-09

2, orange, 1, piece, 30, 2020-06-11

I would want it to  return:

1, apple, 1, ounce, 10, 2020-06-10

2, orange, 1, piece, 30, 2020-06-11

 

I assume I need to use an inner join in the ExecuteSQL function, and have MAX(log_date) as one of the join fields, but can't seem to get it to work.

[CoachMan]

I was able to populate the table with distinct foods via a looping script after loading the virtual list.  It's a small data set, so it's plenty quick doing it this way.  Still curious how to handle it via the ExecuteSQL function though if anyone wants to take a stab at it.

[Wim Decorte]

You're really asking two things and it is important to keep them apart:

1) give me a list of distinct fruits

2) for each fruit in that distinct list, show me the most recent entry

 

Because these are two different questions, you can solve them separately instead of trying to come up with one equation (SQL syntax) that does both.  It will also help in troubleshooting and maintaining the code if you do this separately.

Also: trying to do this as one SQL Query may be slow, executesQL() is blazingly fast for simple queries but gets slower the more JOINs and SQL functions you put in the query.

For those reasons I would use SQL to just get the list of distinct fruits and use a relationship from the virtual table to the actual 'transactions' table sorted on the transaction date in reverse order, and show the related data on the virtual list layout.

 

[comment]

13 hours ago, CoachMan said:

I assume I need to use an inner join in the ExecuteSQL function, and have MAX(log_date) as one of the join fields, but can't seem to get it to work.

I am not sure Filemaker SQL supports a join to a subquery (i.e. a derived table).

 

12 hours ago, CoachMan said:

I was able to populate the table with distinct foods via a looping script after loading the virtual list.

I don't see why you need all this complexity when you could simply produce a summary report directly from your data table. Just sort the records by food_name and by log_date and show them in a layout with a sub-summary part by log_date and no body part. Or have you script loop and omit all records except the first (or last) record in each group (this can be done very quickly by adapting the Fast Summaries method). 

[CoachMan]

58 minutes ago, comment said:

I don't see why you need all this complexity when you could simply produce a summary report directly from your data table. Just sort the records by food_name and by log_date and show them in a layout with a sub-summary part by log_date and no body part. Or have you script loop and omit all records except the first (or last) record in each group (this can be done very quickly by adapting the Fast Summaries method). 

I was advised on another thread to use the virtual list technique because I needed the categories (breakfast, am snack, lunch, etc) to show even if there are no foods in them (preferably without creating a blank record for each meal for each log date).  That way the user can tap on the meal name to add foods to the meal.  It's also extremely slow running all the calculations over the network, to recalculate calories and macronutrients each time a portion is incremented or unit of measure changed...so the virtual list has solved that issue.  I've set all of that up already and it's working much better overall than when I based the list view layout directly on the data table, although it is complex.

I'm pulling data from different tables depending on whether the user clicks the button to load all available foods (it shows omitted only so that none are shown until they type something into a search box), or the button to load recently logged foods, or the button to load available recipes.  I have the first one all set up already and am working on the 2nd one (recently logged foods).  What seems to have worked (per my 2nd post last night) was to use ExecuteSQL to populate the virtual list, then sort by food_id and log_date,  and then loop through the list to omit any duplicate foods.  I'm only showing the first 30 results, so it's loading the list in less than a second.

8 hours ago, Wim Decorte said:

You're really asking two things and it is important to keep them apart:

1) give me a list of distinct fruits

2) for each fruit in that distinct list, show me the most recent entry

 

Because these are two different questions, you can solve them separately instead of trying to come up with one equation (SQL syntax) that does both.  It will also help in troubleshooting and maintaining the code if you do this separately.

Also: trying to do this as one SQL Query may be slow, executesQL() is blazingly fast for simple queries but gets slower the more JOINs and SQL functions you put in the query.

For those reasons I would use SQL to just get the list of distinct fruits and use a relationship from the virtual table to the actual 'transactions' table sorted on the transaction date in reverse order, and show the related data on the virtual list layout.

 

I'm not 100% sure a relationship would work for my situation as I already have the virtual list table's fields (calories, protein, carbs, gram portion, unit grams, etc) set up to grab the values from the list (it's already set up to pull from the main foods table to show all available foods).  And several scripts referencing these fields to run all the calculations as portions are incremented, units of measure changed, etc.  But the solution I mentioned in my 2nd post last night is working great...I'm only showing the most recent 30 foods, so the loop script and everything runs in less than a second.