Find the second lowest.

[DaniR86]

I am trying to add a new field to my data that lists the second lowest number in a data set.

So if I have

New York 100
New York 300
New York 150
Florida 20
Florida 23
Florida 17
Las Vegas 70
Las Vegas 78
Las Vegas 55

I would like to see...

New York 150
Florida 20
Las Vegas 70

The search I have has quite a lot in it already so building a new field with an eval statement near the start will probably be my best option.

Thanks in advance for any help x

[Richfez]

DaniR86, did either of the below help you get to your answer?

If so, could you please mark one as answered?

If not, give us a bit more detail about how far you've gotten and maybe we can help you with the rest!

[sundareshr]

Try this (assuming two fields shown in your example are city and count)
Added missing pipe

... | sort count | streamstats count as c by city | where c=2 | fields - c

[Richfez]

Add to your existing search...

... | top limit=2 MyField | rare limit=1 MyField

Obviously, put your field name in for MyField.