How to display ONLY first row for each value in table

[mprreddy51]

Hi,

I have a requirement to use display first row for every ACCNO.any Ideas?

query:
I used some transaction command |table ACCNO,VALUE

ACCNO VALUE
1 100
1 110
2 125
2 134
2 143
3 156
3 123
4 124
5 567
5 129
5 345

EXPECTED OUTPUT:

ACCNO VALUE
1 100
2 125
3 156
4 124
5 567

[somesoni2 · Accepted solution]

Try any of these

 some transaction command |table ACCNO,VALUE | dedup ACCNO

 some transaction command |table ACCNO,VALUE | stats first(VALUE) as VALUE by ACCNO

[mprreddy51]

@somesoni2

Hi Somesh,

can we use first(duration) or last(duration) in timechart command?I want to pick only first value in duration

like sampledata:

_time duration
2016-08-02 12:00:00 11.848000
12.031000
query:
transaction command| timechart span=1m list(duration) as duration

thanks.

[somesoni2]

You're using list function with timechart, so you'd be getting a multivalued field duration for minutes where there is multiple duration. This list will be sorted by the time. So if you want to pick up only a single value, first or last, from this multivalued list, try like this for getting first/oldest duration for that min

transaction command| timechart span=1m list(duration) as duration | eval duration=mvindex(duration,0) 

Replace| eval duration=mvindex(duration,0) with | eval duration=mvindex(duration,-1) for last/latest duration for that min