Sql syntax in Model builder

Question

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.

• Brad Nesom: case sensative IN ?
• blah238: What is the underlying DBMS?

Answer 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.

Answer 2

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.