Using MOD within a GROUP BY subquery on point dataset

Question

I have a dataset of 200 points (in shp/f gdb/p gdb format) in ArcGIS.

I am trying to use the MOD() function within a GROUP BY subquery as a definition query on the layer to thin out the data. What I need is to group the data by the 'Name' column, and then filter each 'Name' by a specific amount or percentage. Ideally I would like to use a MOD value of 5 or 10.

The table below is an example.

ID | Time | Name
---|------|-----
01 | 0100 | FOX
02 | 0200 | FOX
03 | 0300 | FOX
04 | 0400 | FOX
05 | 0600 | FOX
-
06 | 0100 | CAT
07 | 0200 | CAT
08 | 0300 | CAT
-
09 | 0100 | DOG
10 | 0200 | DOG
11 | 0300 | DOG
12 | 0400 | DOG
13 | 0500 | DOG
14 | 0600 | DOG
15 | 0700 | DOG

The below MOD works on the entire layer, but there is a risk of it skipping data like CAT where the query only shows rows 1, 5, 10, and 15 (file gdb format).

MOD("ID", 5) = 0

The below also works, but to a lesser degree. I need to thin the data, instead of just using the MIN and MAX values (personal gdb format).

[Time] in (SELECT MAX( [Time] ) 
FROM data_table GROUP BY [Name])

What would be the best way to achieve this? Is there a simpler way of doing it that I have overlooked?

Answer 1

You can use ArcMap's [Make Query Table] geoprocessing tool to:

1. Set input table to your points data.
2. Select need fields(you must select Shape field if you want get result with feature)
3. In the where_clause,input as your question([Time] in (SELECT MAX( [Time] ) FROM data_table GROUP BY [Name]))
4. Set table name if you not need default out table name.
5. Click Ok button,you should find a new layer that you want thin out.
6. You can export it to shp/f gdb/p gdb format.