SQL Select Query

1853
5
11-29-2011 11:17 AM
JontyKnox
New Contributor II
Hi there,

I am trying to construct an SQL query using the 'Select' tool. Essentially what I am trying to figure out is how to select the first row in an attribute table, with an additional statement only allowing this record to be selected if my Boolean field (in the same attribute table) is equal to 0.

For a bit of background, this step fits into a wider process that I am running in Model builder. The tool factors into a structure that would then create a buffer around the highest ranked points (FID moving from 0 down) in the attribute table. It would then exclude those points that fall within the buffered regions around the highest ranked points (by assigning them a value of 1). This process would repeat until all points had a Boolean field value of 1. 

If you guys could lend me a hand that would be great,

Cheers, Jonty
0 Kudos
5 Replies
BruceHarold
Esri Regular Contributor
Hi Jonty

Your SQL query will be something like; where...:

"ObjectID" = (select min("ObjectID") from Mytable) and "Otherfield" = 0

Depending on the host database you're working in the syntax may need to be tweaked.
See the help on subqueries.

Regards
0 Kudos
JontyKnox
New Contributor II
Hi Bruce,

So I constricted this from what you gave me, I've tweaked the database slightly since my last post, but the statement accurately reflects what I want to do:

SELECT MAX( "Departures" ) FROM SORT AND "Boolean" =0

However, when I verify the statement it still gives me the 'Invalid SQL statement was used' (twice). Any advice what I've messed up with the syntax?

Thanks, Jonty
0 Kudos
BruceHarold
Esri Regular Contributor
Hi Jonty

Is "SORT" a reserved word in your DBMS?

Also, the parenthese around the subquery (select...) will be required.

Cheers
0 Kudos
JontyKnox
New Contributor II
Thanks for the quick reply. SORT is the table name and is not a reserved word in my DBMS.

I just broke the query down to basics and the Boolean part works, as does the 'departures' section- only if I change it from Max to some other function such as >10 however.

This leads me to think that perhaps the problem is with the MAX function?

Cheers,
0 Kudos
BruceHarold
Esri Regular Contributor
Jonty

What is your host DBMS, or are you using file GDB?  Email me at bharold@esri.com and i'll determine the correct syntax.

Regards
0 Kudos