3

I am trying to use select by attribute for the table in ArcGIS, which is similar to the following picture (lets say):

enter image description here

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?

2

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.

1
  • While the Summary Statistic tool is a hint in the right direction (see also), the extract from ESRI's website plainly states that this type of query should be possible using a subquery.
    – nagytech
    May 31 '15 at 0:53
1

According to all of the documentation provided by the vendor, the following SQL features are supported using a subquery:

  • IN
  • GROUP BY
  • MAX

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)
2
  • 1
    Been tinkering around and I managed to get your SQL syntax count in (select max(count) from table group by id) to work but ONLY in a PERSONAL geodatabase. The file geodatabase would not accept the syntax. Interesting!
    – Hornbydd
    Jun 1 '15 at 21:41
  • The ESRI docs does say something to that extent (ie. only personal gdb will support sub- queries / grouping). TBH, I don't understand why ESRI preformats the query on us.
    – nagytech
    Jun 1 '15 at 22:09
0

I don't know if this is what you are looking for, but something like:

Select id, max(count) from table group by id
1
  • 1
    In a perfect world, yes that would work. But, ESRI is a bit silly with their SQL implementation.
    – nagytech
    May 31 '15 at 0:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.