SQL Challenge

SQL is the language we use to do our every day queries. SQL stands for Structured Query Language and there is an enormous amount of help to be found on the internet for this language. For most of us we use the SQL with a simple statement such as [fieldname] = “somevalue” and this is sufficient to get the job done, but SQL will do so much more.

When beginning in SQL [fieldname] = “somevalue” is an effective way to search for and find the features we are after. There are other statements that can be more generic such as [fieldname] Like “the beginningofsomevalue%” or [fieldname] Like “%theendofsomevalue” that will return the attributes that begin with some value and the % sign is a wildcard character that represents any number of characters that follow. For example if your SQL statement was [fieldname] Like “B%” then any value in the field that began with letter “B” would be selected. The % sign at the end of the some value statement requests for all characters that follow, but % sign at the beginning of the some value statement requests all characters that precede the value.

I think that most of the useful operators are on our standard “Select by Attributes” dialog box. There are some however that are not on the dialog box that are still available for use. Such as [fieldname]  IN( ‘somevalue’, ‘someothervalue’) or [fieldname]  BETWEEN somevalue AND someothervalue.

Here is a much more complicated query which finds duplicate records [fieldname] In (SELECT [fieldname] FROM [tablename] As Tmp GROUP BY [fieldname] HAVING Count(*)>1 )

So this is your mission, should you choose to accept it. In the comments area below demonstrate and explain a statement not already used. If the statement is complex submit a post where we all may learn.

Advertisements
Leave a comment

1 Comment

  1. The SQL used in Arc is much different than standard Transact SQL, for instance in Transact SQL the wildcard is a “*”. Also when writing a definition query in Arc it’s important to remember that what you are writing falls into the “Where” clause of the SQL statement. So a query like “SELECT [Fieldname] FROM [Tablename] WHERE [Fieldname] = something” would be represented in Arc with only the WHERE portion of “[Fieldname] = something”.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: