How to reference a field value in a drop-down from a specific row within a table?

[HayesM24]

Problem
I am measuring stored procedure hits by system codes. I am trying to implement 5 panels in one row that show graphs and single values for each top 5 system code in the data based on the number of Hits (sum of the SQL count). I would like to populate the drop-down values dynamically based on which subsystem is picked and what time range is chosen.

For each of the 5 drop-down values to be selected from first to fifth in order, my first solution was to find the top system code and append the rest of the system codes. Then in the next panel find the second top system code and append the rest, and so on for all 5 panels. This caused my search to find the results twice as it ran through the results to pick out one system code, then another time to append the rest of the system codes after.

Possible Solutions
Is there a way I can organize the data to have the X row become the first row in the table?

Or in the drop-down code, am I able to reference the second row? Ex. $row.SysCode$ will select the value in field SysCode on the first row, however, I need this to be done for the second row.

Current Search

| savedsearch which uses subsystem and timerange filters and returns SysCodes... | stats sum(SQL_CNT) as Hits by SysCode | sort -num(Hits) | head $head_num$ | tail 1 | append [| savedsearch which uses subsystem and timerange filters and returns SysCodes... | stats sum(SQL_CNT) as Hits by SysCode | sort -num(Hits) ] | dedup SysCode

[sundareshr · Accepted solution]

How about something like this

| savedsearch which uses subsystem and timerange filters and returns SysCodes... | stats sum(SQL_CNT) as Hits by SysCode | sort -num(Hits) | streamstats count | eval count=if(count=$headnum$, count=0, count) | sort count

[HayesM24]

Thank you! This worked. Specificall the fix I needed was:

count=if(count=$headnum$, count=0, count) should be count=if(count=$headnum$, 0, count)

[sundareshr]

You're right

[sundareshr]

Please accept the answer to close it out.