How can i get the top 3 rows from each group in a table.
Here is sample data output from my query
The output is result of
..| stats sum(fieldB) by fieldA, fieldC,fieldD,fieldE | table fieldA, sum(fieldB),fieldC,fieldD,fieldE | sort fieldA, -sum(fieldB)
fieldA sum(fieldB) fieldC fieldD fieldE
111 1500 abc def ghi
111 1000 yui mmm ybv
111 800 abc def yui
111 700 qwe sdf ghi
111 500 sdf def iuo
222 3500 yui hgu lop
222 2800 abc mmm ghi
222 1400 hji sdf lok
222 900 yui def mmm
333 3000 abc def nnn
333 1200 mmm edr yui
333 300 abc def ghi
333 100 mmm yui ghi
Now, how can I get top 3 rows from each of fieldA group ? Output should be as below
fieldA sum(fieldB) fieldC fieldD fieldE 111 1500 abc def ghi 111 1000 yui mmm ybv 111 800 abc def yui 222 3500 yui hgu lop 222 2800 abc mmm ghi 222 1400 hji sdf lok 333 3000 abc def nnn 333 1200 mmm edr yui 333 300 abc def ghi
How about in this?
..| stats sum(fieldB) by fieldA, fieldC,fieldD,fieldE | table fieldA, sum(fieldB),fieldC,fieldD,fieldE | sort fieldA, -sum(fieldB)| dedup 3 fieldA
How about in this?
..| stats sum(fieldB) by fieldA, fieldC,fieldD,fieldE | table fieldA, sum(fieldB),fieldC,fieldD,fieldE | sort fieldA, -sum(fieldB)| dedup 3 fieldA
That did the trick. Thanks much..