4

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.

  • case sensative IN ? – Brad Nesom Mar 14 '13 at 14:49
  • 1
    What is the underlying DBMS? – blah238 Mar 14 '13 at 21:32
1

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.

0

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 <layer> WHERE  <column> IN ('item1', 'item2', etc.)

Use the apostrophe for text, and none for numbers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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