SELECT with filter and order by

[Greg_L]

For some reason I cannot figure out how to combine or use the needed expressions to get the list I need. Here is what I am looking to select (in more of a SQL format):

SELECT TOP 2 [Quantity]
FROM Batch Details
WHERE AND([Batch ID]=[_THISROW].[Batch ID], [Code]=[_THISROW].[Code], [Offer]=[_THISROW].[Offer])
ORDER BY [_RowNumber] DESC

How would I write that using an AppSheet expression? Once I have those two Quantity values, I will wrap them in a SUM() function. So, in other words, I want the two most recent Quantity values that meet the given filtering criteria. Thanks for any help!

[Greg_L]

@Steve I’m sure you could figure this out pretty quick. I hope you don’t mind me knocking on your door about this. I’d be very grateful for your help.

[Jonathon]

Something like this:

TOP(
  ORDERBY(
    FILTER("Batch Details",
      AND(
        [Batch ID]=[_THISROW].[Batch ID],
        [Code]=[_THISROW].[Code],
        [Offer]=[_THISROW].[Offer]
      )
    ),
  [_RowNumber], TRUE),
2)

Edit: this will return the top 2 row keys; I suppose you wanted the quantity values of these.

You could use a secondary virtual column with INDEX() against this. I’m having a hard thing thinking how to return the top two quantity values as you are describing in a single formula, without having to do two expensive searches.

[Suvrutt_Gurjar]

I believe it may need to be done in 2 steps.

TOP 2 ROWS : Find the two largest row numbers having the described criteria, Say this list type column is called [Top 2] with following expression something like below

TOP(SORT( SELECT( Batch Details[_RowNumber], AND([Batch ID]=[_THISROW].[Batch ID], [Code]=[_THISROW].[Code], [Offer]=[_THISROW].[Offer])), TRUE),2)

VC 2 SUMQuatities : Then find the qunatities with these two largest rownumbers and add these in another VC

SUM(SELECT(Batch Details[Quantity], IN([_ROWNUMBER], [TOP 2]))

[Steve]

If I understand what you want:

ORDERBY(
  TOP(
    ORDERBY(
      FILTER(
        "Batch Details",
        AND(
          ([_THISROW].[Batch ID] = [Batch ID]),
          ([_THISROW].[Code] = [Code]),
          ([_THISROW].[Offer] = [Offer])
        )
      )
      [Quantity],
      TRUE
    ),
    2
  ),
  [_ROWNUMBER],
    TRUE
)

Of the matching batch detail rows, get the 2 with the highest quantity values, then order those two rows by their original row numbers in reverse order (newest first).

Alternatively, grab the two newest of the matching rows, then order by quantity (highest first):

ORDERBY(
  TOP(
    ORDERBY(
      FILTER(
        "Batch Details",
        AND(
          ([_THISROW].[Batch ID] = [Batch ID]),
          ([_THISROW].[Code] = [Code]),
          ([_THISROW].[Offer] = [Offer])
        )
      )
      [_ROWNUMBER],
      TRUE
    ),
    2
  ),
  [Quantity],
    TRUE
)

[Greg_L]

It seems everyone is having the same problem I had. I need the Quantity value from the filtered records, not the ID (key) value. I think I must require this in one expression because I’m using it in a Action where I am setting the Quantity field of a certain row equal to this expression I’m trying to write. I need to SUM() the two most recent Quantity values that meet the given filtered and sorted criteria.

If only the SELECT statement had options for filtering and sorting…

Or if the ORDERBY or FILTER expressions had the ability to return a value other than the key…

[Steve]

Your pseudo-code uses both TOP and ORDER BY. What is the goal for each of those?

[Greg_L]

The ORDERBY is to sort the filtered records by _RowNumber in descending order, showing most recent records at the top.

TOP takes those 2 most recent Quantity values. I don’t need to sort those Quantity values. I need to SUM() them.

[Steve · Accepted solution]

Next time, please skip the pseudo-code and go straight to the narrative description.

SELECT(
  Batch Details[Quantity],
  IN(
    [_ROWNUMBER],
    TOP(
      SORT(
        SELECT(
          Batch Details[_ROWNUMBER],
          AND(
            ([_THISROW].[Batch ID] = [Batch ID]),
            ([_THISROW].[Code] = [Code]),
            ([_THISROW].[Offer] = [Offer])
          )
        ),
        TRUE
      ),
      2
    )
  )
)

[Greg_L]

That’s it!! Thank you! I’ll be more descriptive next time. I tend to think in SQL and then try to convert that into AppSheet expressions. I would have never come up with that complicated expression. It’s very simple in SQL.