5

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.

2
  • 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. 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. Feb 14 '11 at 16:32
3

Try:

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

enter image description here

3
  • 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
3

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)"

4
  • 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
  • I tried the syntax but it shows error An invalid SQL statement was used my query statement looks like that "AREA_M=SELECT DISTINCT MAX(numfield) FROM table)"
    – poshan
    Aug 4 '15 at 17:13
  • you forgot left parenthese,correct the statement like this: "AREA_M=(Select Max(your_numeric_Field_Name) From table)"
    – Reza
    Aug 6 '15 at 4:07
1

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.

1
  • 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
0

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.

2
  • 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. Feb 14 '11 at 15:33
  • @Kirk: It should work now, link mixup
    – scw
    Feb 14 '11 at 17:53

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.