Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm working in ArcMap 9.3 and I've never dealt with that kind of queries...

I need to Select the MAX value from column [A] for each set of records in the table which have the same value in column [B]

For example

[A]  [B]

100  aaa   

101  aaa

102  aaa

999  aaa

100  bbb

101  bbb

102  bbb

999  bbb

so the selection would be record#4 (where [A]=9999 which is the biggest value of all 'aaa') and record#8 (where [A] = 999 which is the biggest of all 'bbb')

Is it possible to do that using queries and subqueries when working with databases?

Any help appreciated! Dan

share|improve this question

2 Answers 2

up vote 5 down vote accepted

Do you just need to know the max value for each unique entry in column B, or do you actually need to select the features with the maximum values on your map?

If you just need an output table with headings [B], [Max_of_A], you can open the attribute table, then right-click column B and choose Summarize. In the list of summary statistics to be calculated, choose Maximum for column A and you should end up with the output you need.

If you need to actually select the features on your map, one way would be to join the table produced by Summarize back to your original features. Let me know if you'd like more details.

Someone else may have a better way, but I don't think you can do this directly using ArcMap's Select by Attributes option(?). Would be interested to be proved wrong, though.

Edit

As @GIS-Jonathan says, if your data is stored in a spatial database and you're just using ArcMap to view results etc. then your best bet is to use a GROUP BY query as per his method. If you're working with a shapefile or a feature class stored in a file or personal geodatabase, the method below should work, but it's a bit of a faff! 300,000 records shouldn't be a problem, though.

Start by using Summarize on your original table. You should get an output something like this:

[B]    [Max_of_A]
aaa    999
bbb    999

Now join this table back to your original dataset using column B as the join field (right-click your original dataset, then Joins and Relates > Join. Choose to keep all records). This should make your attribute table look something like this:

[Orig_A]  [Orig_B]  [Summary_B]  [Max_of_A]
100       aaa       aaa          999
101       aaa       aaa          999
102       aaa       aaa          999
999       aaa       aaa          999
100       bbb       bbb          999
101       bbb       bbb          999
102       bbb       bbb          999
999       bbb       bbb          999

Then you can use Select by Attributes to select all the rows where [Orig_A] = [Max_of_A].

Quite a faff, but I can't think of an easier way to do it if your data isn't already in a spatial database supporting GROUP BY. Maybe someone else can suggest a better way?

Good luck!

share|improve this answer
    
+1 Agree with this. It depends on the nature of the problem. If its only one or two, manually selected statistics per my answer is best. If its lots, then summarize per JamesS is best. The database option I listed would be simplest if you're comfortable in that environment and have lots of results you want tabulated. –  GIS-Jonathan Jul 12 '12 at 10:43
    
Yes, actually I need to select these features. And I have to deal with 300 000 records... –  Dan Volkov Jul 12 '12 at 10:48
    
Please see edit. Might give you some more ideas if GIS-Jonathan's SQL-based approach isn't an option. –  JamesS Jul 12 '12 at 11:52
    
Thanks a lot James, will try this now! –  Dan Volkov Jul 12 '12 at 12:06

Its best to get this data straight from the database if you want it for every COLUMN_B grouping. This query in a database like Oracle would be (other databases will be similar):

select count(*), MAX(column_A), column_B from TABLE_NAME group by column_B

That will give:

4, 999, aaa
4, 999, bbb

To do it in ArcGIS is fairly easy. Use the Select by Attributes to select everything in group AAA or BBB. Then in the attribute table, right click on the column you want the MAX of and select Statistics. This will show the "maximum" which is of the selected rows.

share|improve this answer
    
As said there are more than 300 000 records in the table so I can't do that one by one (set of records) so I still wonder if it's possible to make some query for that. –  Dan Volkov Jul 12 '12 at 10:55
    
Doing it in the database would probably be the best option then. The syntax will be similar to the SQL I provided, you'll need to "group by" your B column. If you just google for "group by" and your database of choice that should get you documentation with specific examples. –  GIS-Jonathan Jul 12 '12 at 10:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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