how to get count numbers based on the values

[jenniferhao]

I have the following fields: x, value, I want to get number that count by value of x.
for example : 267 is the smallest value of 101L1, then the number should be 1, the next one 268 is 2...

x value number
101L1 267 1
101L1 268 2
101L1 269 3
101L1 270 4
101L1 271 5
101L1 272 6
101L1 273 7
101L1 274 8
101L1 275 9
101L1 276 10
101L1 277 11
101L1 278 12
101L1 279 13
101L1 280 14
101L1 281 15

101L2       28     1
101L2       29     2
101L2       30     3
101L2       31     4
101L2       32     5

101L3       27.3    1
101L3       27.4    2
101L3       27.5    3
101L3       27.6    4
101L3       27.7    5
101L3       27.8    6

[jenniferhao]

Thanks, it works for me.

[woodcock · Accepted solution]

Assuming that you also have to properly account for "ties", then this:

| makeresults
| eval _raw="x,value
101L1,267
101L1,268
101L1,269
101L1,270
101L1,271
101L1,272
101L1,273
101L1,274
101L1,275
101L1,276
101L1,277
101L1,278
101L1,279
101L1,280
101L1,281
101L1,267
101L1,268
101L1,269
101L1,270
101L1,271
101L1,272
101L2,28
101L2,29
101L2,30
101L2,31
101L2,32
101L3,27.3
101L3,27.4
101L3,27.5
101L3,27.6
101L3,27.7
101L3,27.8"
| multikv forceheader=1
| table x value

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| sort 0 - value
| streamstats count AS number BY x
| eventstats min(number) AS number BY x value

[to4kawa]

| makeresults count=2
| streamstats count
| eval _time = if (count==2,relative_time(_time,"@m"), relative_time(_time,"-1h@m")) 
| makecontinuous span=1m
| streamstats count
| eval x="101L".(count % 3 + 1), value=random() % 30 + 210
| table x value
`comment("this is sample data")`
| sort 0 value
| streamstats count by x
| sort 0 x count

Hi, @jenniferhao @vnravikumar
reset_on_change=true is not required.
Considering the case of disparity, I tried to count after sorting.

[vnravikumar]

Hi

Try with

... |streamstats count by x reset_on_change=true|table x value count