Help with search query to table single occurrence rows?

[rsathish47]

Hello All,

I have table of data as below. I have to fetch single occurrence row. Please Help

Original Table

ID CA Value GrName UsrName DB Name

X1 CA1 CA1 Usr1 …

X2 CA2 CA2 Usr2 …

X2 CA2 CA2 Usr2 …

X3 CA5 CA5 Usr3 …

X3 CA5 CA5 Usr3 …

X3 CA5 CA5 Usr3 …

X4 CA7 CA7 Usr4 …

X4 CA7 CA7 Usr4 …

X5 CA9 CA9 Usr5 …

Desired Result Table

ID CA Value GrName UsrName DB Name

X1 CA1 CA1 Usr1 …

X5 CA9 CA9 Usr5 …

Thanks

Sathish R

[linu1988 · Accepted solution]

Hello Sathish,
Try the below one.

source=blah.. |stats list(*) as * by ID|eval a=mvcount(CA)|where a=1

This will give you single occurrence of ID field.

Thanks,
L

[rsathish47]

Thanks for the workaround.. It Works

[rsathish47]

Thanks Changwoo.. I need single occurrence row not unique row.

[changwoo]

try to use "| dedup ID" search command