I am trying to use select by attribute for the table in ArcGIS, which is similar to the following picture (lets say):
I want to select those entries for each A and B, which have maximum count.
I used
SELECT *
FROM table
WHERE
count=
(
SELECT DISTINCT MAX(count)
FROM table
)
This can give result for the maximum count of the whole table. What should be the modification or addition in the above query so that it will select the maximum count for A and B as well?
I'm not sure you are able to achieve the selection you want as according to the Help file it says:
...Because you are selecting columns as a whole, you cannot restrict the SELECT to return only some of the columns in the corresponding table because the SELECT * syntax is hard-coded. For this reason, keywords, such as DISTINCT, ORDER BY, and GROUP BY, cannot be used in an SQL query in ArcGIS except when using subqueries
Whilst it's not a selection you can certainly extract the information you want by running the Summary Statistics tool. You would get the max value from your count field and set the case field to be your ID field. This tool would create a new table and using your example above it would extract out A:20 and B:34.
If you really want your data selected then if you pulled out the ObjectID during the summary statistics tool you could join or relate that table back to the original table and do a selection that way.
According to all of the documentation provided by the vendor, the following SQL features are supported using a subquery:
The query that should by all means work would then be:
count in (
select max(count) from table
group by id
)
In 'pure' SQL (as some would put it), you would see this as:
create table temp (
id nvarchar(1),
ct int
)
go
insert into temp
select 'a', 1
insert into temp
select 'b', 1
insert into temp
select 'a', 4
insert into temp
select 'b', 8
select * from temp
where ct in (
select max(ct) from temp
group by id
)
id ct
-----
a | 4
b | 8
-----
drop table temp
Since, for whatever reason, the query isn't working, I've knocked up the following script that works for you. Simply configure it and run it in the Python console, or by any other means you're familiar with.
import arcpy
layer = "pts1"
pkCol = "OBJECTID"
gpCol = "id"
ctCol = "count"
cols = (pkCol, gpCol, ctCol)
pks = []
gps = []
cts = []
i = -1
with arcpy.da.SearchCursor("pts1", cols) as c:
for f in c:
pk = f[0]
gp = f[1]
ct = f[2]
if not (gp in gps):
i = i + 1
gps.append(gp)
cts.append(0)
pks.append(pk)
if (ct > cts[i]):
cts[i] = ct
pks[i] = pk
cts[i] = ct
query = '{0} IN ({1})'.format(pkCol, ', '.join("{0}".format(n) for n in pks))
arcpy.SelectLayerByAttribute_management(layer, "NEW_SELECTION", query)
I don't know if this is what you are looking for, but something like:
Select id, max(count) from table group by id
