Monday, June 11, 2012

Using SQL to Get n number of Rows

When you query spatial data in ArcMap/Catalog/Python, etc.. you cannot limit the row search by default and you can only specify the where part of the sql statement: "SELECT * from < DATASET > WHERE" and then you can specify TYPE = '1'.

Let's assume you want to just get back n number of rows.  If you are using SDE, you could use ArcSDESQLExecute's methods to perform your sql, but you could also use a sub-query. 

Take your default statement that ArcMap allows you to perform on a layer: 

     "Select * from wgs.temp where "

Since we want to just get back n number of rows, use the OBJECTID field as a reference and the 'in' function:

    "Select * from wgs.temp where OBJECT in (select OBJECTID  from wgs.temp where rownum <= 5)"

This will return 5 rows in the data set.  If you want 10, 15, of n number of rows, replace the 5 with whatever value you want.  You can apply this to update and search cursors on SDE data only.