Tell me more ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 3 down vote favorite

Using 'Select By Attributes' how can I select records from a shapefile database table where a numeric field contains the largest value. Need to use MAX function or something like that but can't get the syntax right.

share|improve this question
1  
I do not think it is possible with select by attributes. The easiest way is to order your attribute table by the column in question and then select the first record. – Matthew Snape Feb 14 '11 at 14:35
3  
What GIS software are you using? That would make it a lot easier for people to answer your question. – RyanDalton Feb 14 '11 at 16:32

4 Answers

up vote 2 down vote

Try:

SELECT * FROM table
WHERE numfield=(SELECT DISTINCT MAX(numfield) FROM table)

enter image description here

share|improve this answer
The second one will certainly fail. The first one may not work in all databases. – johanvdw Feb 14 '11 at 23:17
You are right, the second fail. The first worked on postgis. I'am deleting the second. thanks. – Pablo Feb 15 '11 at 11:43
Thanks this actually works, I was just having a problem with syntax. Seems odd that you have to add the shapefile or table name when it is already open as the active doc. – Bahny Feb 16 '11 at 1:25
up vote 2 down vote

if using ArcMap,Pablo has suggested a rigth query syntax,pay attention only where clause must be entered in "select by attribute" dialog,mean this phrase:

"numfield=(SELECT MAX(numfield) FROM tablename)"

share|improve this answer
Thanks this actually works, I was just having a problem with syntax. Seems odd that you have to add the shapefile or table name when it is already open as the active doc. – Bahny Feb 16 '11 at 1:25
"numfield=(SELECT MAX(numfield) FROM tablename)" i have been try above syntax but one time i get max result but another time do not give result but give only warnning that general expression is invaild what the reson anobody know wht happend? – user3223 Jun 4 '11 at 8:36
up vote 1 down vote

If you are using Esri ArcGIS, then you can use Summary Statistics to calculate the maximum value (as well as min, mean, etc). Then you can use Select By Attributes to select the records that match the max value output by the Summary Statistics tool.

Since you are selecting from a Shapefile, there is no way through a SQL-like syntax to retrieve the max value. I imagine you are trying something like COLUMN = (SELECT MAX(COLUMN) FROM SHAPEFILE) which simply will not work.

share|improve this answer
1  
Thanks this actually works, I was just having a problem with syntax. Seems odd that you have to add the shapefile or table name when it is already open as the active doc. – Bahny Feb 16 '11 at 1:24
up vote 0 down vote

You haven't specified what software you're using, but based on the use of the term 'select by attributes' we can guess ArcGIS. If you're using 9.x, you can use this code to calculate the maximum in VBA, or if you're on 10, you could write a simple python analog.

share|improve this answer
I don't think the referenced approach works with shapefiles though. But you're right, it should be possible to write a fairly simple script. – Kirk Kuykendall Feb 14 '11 at 15:33
@Kirk: It should work now, link mixup – scw Feb 14 '11 at 17:53

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.