Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

In ModelBuilder I am trying to perform the following query, using the select by attribute tool. I am having great difficulty with the sql syntax.

What I am trying to do is

Select Top 3 (Avg) Price, GasID Group by GASID order by GASID

The help indicates that aggregrate functions need to be performed in subqueries only? Is this true.

I don't want a subquery. Only this works but returns no rows and it is not what I am looking for:

"RegGasPrice" = (SELECT MAX( "RegGasPrice") FROM gasprices). There is no "in" so I must use equal.

Any assistance would by greatly appreciated.

Jane

share|improve this question
    
case sensative IN ? –  Brad Nesom Mar 14 '13 at 14:49
1  
What is the underlying DBMS? –  blah238 Mar 14 '13 at 21:32

3 Answers 3

Here is what a definition query would look like that could do this:

-- For 3 highest gas prices
GASID IN (
SELECT TOP 3
GASID
FROM gasprices
GROUP BY GASID
ORDER BY AVG(Price) DESC)

-- For 3 lowest gas prices
GASID IN (
SELECT TOP 3
GASID
FROM gasprices
GROUP BY GASID
ORDER BY AVG(Price) ASC)

You are trying to select features by GASID and not by average price. There is a price field but no average price field. So, here, we select the top 3 GASID's where the average price is one of the top 3 or bottom 3 even though we cannot tell what that average price is in this query. You could obtain the average price by right clicking on the Price field and selecting the "Statistics" item.

share|improve this answer

I think any ArcGIS select statement is treated like a select * from where And the where part is the window that your criteria is placed. So for your statement try something like this: SELECT top 3 * from WHERE IN ('item1', 'item2', etc.) Use the apostrophe for text, and none for numbers.

share|improve this answer

When I posted this it removed a few items because I used symbols to show them as variables. Here's the example: SELECT top 3 * FROM Table WHERE Column IN ('item1', 'item2', etc...)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.