topic Get rows from the intermediate table by max value of a column in Splunk Search https://community.splunk.com/t5/Splunk-Search/Get-rows-from-the-intermediate-table-by-max-value-of-a-column/m-p/137054#M184764 <P>I have an <STRONG><EM>intermediate</EM></STRONG> table from some query:</P> <P>... | table Stock_price_difference, start_time, end_time, company</P> <TABLE> <TBODY><TR><TH>Stock_price_difference</TH><TH>start_time</TH><TH>end_time</TH><TH>company</TH></TR> <TR><TD>1.3</TD><TD>10:01</TD><TD>10:20</TD><TD>Apple</TD></TR> <TR><TD>0.3</TD><TD>11:00</TD><TD>11:05</TD><TD>Apple</TD></TR> <TR><TD>1.1</TD><TD>11:30</TD><TD>11:35</TD><TD>Apple</TD></TR> <TR><TD>0.4</TD><TD>09:10</TD><TD>09:18</TD><TD>MS</TD></TR> <TR><TD>0.8</TD><TD>09:50</TD><TD>10:00</TD><TD>MS</TD></TR> <TR><TD>0.1</TD><TD>10:00</TD><TD>10:10</TD><TD>MS</TD></TR> <TR><TD>1</TD><TD>10:15</TD><TD>10:20</TD><TD>Amazon</TD></TR> <TR><TD>1.1</TD><TD>11:10</TD><TD>11:15</TD><TD>Amazon</TD></TR> </TBODY></TABLE> <P>I would like to get the rows by max stock price difference for each company:</P> <TABLE> <TBODY><TR><TH>Stock_price_difference</TH><TH>start_time</TH><TH>end_time</TH><TH>company</TH></TR> <TR><TD>1.3</TD><TD>10:01</TD><TD>10:20</TD><TD>Apple</TD></TR> <TR><TD>0.8</TD><TD>09:50</TD><TD>10:00</TD><TD>MS</TD></TR> <TR><TD>1.1</TD><TD>11:10</TD><TD>11:15</TD><TD>Amazon</TD></TR> </TBODY></TABLE> <P>How can I do that?</P> <P>Thanks.</P> Mon, 11 Nov 2013 17:30:48 GMT harrychen 2013-11-11T17:30:48Z Get rows from the intermediate table by max value of a column https://community.splunk.com/t5/Splunk-Search/Get-rows-from-the-intermediate-table-by-max-value-of-a-column/m-p/137054#M184764 <P>I have an <STRONG><EM>intermediate</EM></STRONG> table from some query:</P> <P>... | table Stock_price_difference, start_time, end_time, company</P> <TABLE> <TBODY><TR><TH>Stock_price_difference</TH><TH>start_time</TH><TH>end_time</TH><TH>company</TH></TR> <TR><TD>1.3</TD><TD>10:01</TD><TD>10:20</TD><TD>Apple</TD></TR> <TR><TD>0.3</TD><TD>11:00</TD><TD>11:05</TD><TD>Apple</TD></TR> <TR><TD>1.1</TD><TD>11:30</TD><TD>11:35</TD><TD>Apple</TD></TR> <TR><TD>0.4</TD><TD>09:10</TD><TD>09:18</TD><TD>MS</TD></TR> <TR><TD>0.8</TD><TD>09:50</TD><TD>10:00</TD><TD>MS</TD></TR> <TR><TD>0.1</TD><TD>10:00</TD><TD>10:10</TD><TD>MS</TD></TR> <TR><TD>1</TD><TD>10:15</TD><TD>10:20</TD><TD>Amazon</TD></TR> <TR><TD>1.1</TD><TD>11:10</TD><TD>11:15</TD><TD>Amazon</TD></TR> </TBODY></TABLE> <P>I would like to get the rows by max stock price difference for each company:</P> <TABLE> <TBODY><TR><TH>Stock_price_difference</TH><TH>start_time</TH><TH>end_time</TH><TH>company</TH></TR> <TR><TD>1.3</TD><TD>10:01</TD><TD>10:20</TD><TD>Apple</TD></TR> <TR><TD>0.8</TD><TD>09:50</TD><TD>10:00</TD><TD>MS</TD></TR> <TR><TD>1.1</TD><TD>11:10</TD><TD>11:15</TD><TD>Amazon</TD></TR> </TBODY></TABLE> <P>How can I do that?</P> <P>Thanks.</P> Mon, 11 Nov 2013 17:30:48 GMT https://community.splunk.com/t5/Splunk-Search/Get-rows-from-the-intermediate-table-by-max-value-of-a-column/m-p/137054#M184764 harrychen 2013-11-11T17:30:48Z Re: Get rows from the intermediate table by max value of a column https://community.splunk.com/t5/Splunk-Search/Get-rows-from-the-intermediate-table-by-max-value-of-a-column/m-p/137055#M184765 <P>This should solve the problem:</P> <PRE><CODE>..| table Stock_price_difference, start_time, end_time, company|sort - company,Stock_price_difference| eval CountF=1|streamstats sum(CountF) as CountF by company|where CountF&lt;2 | fields - CountF </CODE></PRE> <P>This should give you top 1 Stock_price_difference for each company along with start_time and end_time field (or any field available)</P> Mon, 28 Sep 2020 15:14:54 GMT https://community.splunk.com/t5/Splunk-Search/Get-rows-from-the-intermediate-table-by-max-value-of-a-column/m-p/137055#M184765 somesoni2 2020-09-28T15:14:54Z Re: Get rows from the intermediate table by max value of a column https://community.splunk.com/t5/Splunk-Search/Get-rows-from-the-intermediate-table-by-max-value-of-a-column/m-p/137056#M184766 <P>They are many approaches, here is the quick and hack :<BR /> - using a sort and a dedup on the company, to keep the event highest value in the Stock-price-difference per company.</P> <P><CODE>...| sort company -Stock_price_difference | dedup company | table Stock_price_difference, start_time, end_time, company</CODE></P> <P>result is : <BR /> <CODE>Stock_price_difference start_time end_time company<BR /> 1.1 11:10 11:15 Amazon<BR /> 1.3 10:01 10:20 Apple<BR /> 0.8 09:50 10:00 MS<BR /> </CODE></P> Mon, 11 Nov 2013 19:05:30 GMT https://community.splunk.com/t5/Splunk-Search/Get-rows-from-the-intermediate-table-by-max-value-of-a-column/m-p/137056#M184766 yannK 2013-11-11T19:05:30Z Re: Get rows from the intermediate table by max value of a column https://community.splunk.com/t5/Splunk-Search/Get-rows-from-the-intermediate-table-by-max-value-of-a-column/m-p/137057#M184767 <P>Awesome! That works.</P> Mon, 11 Nov 2013 19:18:07 GMT https://community.splunk.com/t5/Splunk-Search/Get-rows-from-the-intermediate-table-by-max-value-of-a-column/m-p/137057#M184767 harrychen 2013-11-11T19:18:07Z Re: Get rows from the intermediate table by max value of a column https://community.splunk.com/t5/Splunk-Search/Get-rows-from-the-intermediate-table-by-max-value-of-a-column/m-p/137058#M184768 <P>That's even sleeker, thanks!</P> Mon, 11 Nov 2013 23:12:11 GMT https://community.splunk.com/t5/Splunk-Search/Get-rows-from-the-intermediate-table-by-max-value-of-a-column/m-p/137058#M184768 harrychen 2013-11-11T23:12:11Z