How to select a number of rows based on current sort order

Question

I would like to be able to select a certain number of rows from my attribute table based on their ordering and not their attributes. For example, if I sort by ObjectID, I then want to select the top 100 rows. If I then alphabetise the Name field, I want to be able to select the top 150 rows.

According to the help files on building a query expression:

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.

In this SO question it shows how to achieve this using a subquery, but it seems to depend on the WHERE clause being part of the subquery.

• blah238: What is your underlying DBMS (Oracle, File geodatabase, etc.)?
• Cindy Jayakumar: @blah238 It is a gdb table in a file gdb.

Answer 1

Have you considered using arcpy, a searchCursor, and a SelectLayerByAttribute?

My idea is that you would sort the data in the attribute table, or however you please, then using arcpy create a searchCursor on the layer you are interested in, then iterate through the searchCursor and use SelectLayerByAttribute to select the current layer using its OBJECTID (or whatever field you choose) - just make sure that you set the selection type parameter to "ADD_TO_SELECTION". Something along these lines:

 rows = arcpy.SearchCursor(lyr_DerivedBoundaries)
 row = rows.next()
while x < 10:
 a = row.OBJECTID
 arcpy.SelectLayerByAttribute_management("Derived_Boundaries","ADD_TO_SELECTION", "[OBJECTID] = " + str(a))
 row = rows.next()
 x += 1

Bear in mind I have not tested or debugged that bit of code, I just thought it would provide a more clear picture of what I was thinking.

I hope that is helpful!

• blah238: Disregarding the try without except/finally clause, this code will run in an infinite loop. I do not recommend using it :)
• Kevin: Hence my disclaimer at the bottom, I edited it to include an increment to x

Answer 2

Do you want to manually apply multiple sort and selections to an attribute table? This technique may help. Sort and Select the desired rows in Table View, save the selection to a Layer by Right-clicking the layer, Selection, Create Layer From Selected Feature. Apply another sort and selection to the new layer. Repeat. This does Not change the original data.

• Cindy Jayakumar: I asked this question because I wanted to know if there was a way to do it using SQL. Judging by the answers and links, it seems like it cannot done using a subquery on a file gdb.

Answer 3

There was a duplicate post here.

Essentially, just sort your table based on the desired attribute. Then, at the bottom of the attribute table, type in the record you want to go to (100, 150, etc). Then, click on the square at the left of the row to highlight the row and drag up to the top.

Alternatively, you could create a new field for the rowid and update it whenever you sort the table. Then, select by attributes for values less than or equal to 100.

There is also, this post which may help. I haven't tried it though. It may depend on the underlying database type.

• Cindy Jayakumar: I was doing it as you mentioned in your first method, but that started getting annoying. I did end up using your second method yesterday as I needed to get the work done.