1

I need to select the top 3 records from a table, but depending on an inline variable from an iteration, occasionally one of those top 3 records should not be selected and should be replaced by the top 4th.

I'm using sql expression in a personal geodatabase with Arc.

So far I have this:

SELECT * FROM MyTable WHERE
[Field1] in(SELECT TOP 3 ([Field1])
FROM MyTable
ORDER BY [Field1] DESC)
AND NOT [Field2] = '%Variable%'

This just selects the top three or the top 2 when [Field2] equals an inline variable. I'm a syntax sql NOOB, but I have a basic understanding of what can be done and that it is probably a simple solution.

Maybe:

If
Count([Field1] in(SELECT TOP 3 ([Field1])
FROM MyTable
ORDER BY [Field1] DESC)
AND NOT [Field2] = '%Variable%') = 3
SELECT * FROM MyTable WHERE
[Field1] in(SELECT TOP 3 ([Field1])
FROM MyTable
ORDER BY [Field1] DESC)
AND NOT [Field2] = '%Variable%'
END
Else
[Field1] in(SELECT TOP 4 ([Field1])
FROM MyTable
ORDER BY [Field1] DESC) 
AND NOT [Field2] = '%Variable%'
END

Something like that?

  • You shouldn't be writing SQL against the back-end of a personal geodatabase - though it is an access-based system, it is not recommended by esri to do so. – DPSSpatial Oct 24 '14 at 15:19
2

I suggest this:

SELECT TOP 3 *
FROM MyTable
WHERE NOT [Field2] = '%Variable%'
ORDER BY [Field1]

Example

Let's start with an unordered table:

+------------------+------------+
| rainbow_sequence | color_name |
+------------------+------------+
|                2 | orange     |
|                3 | yellow     |
|                1 | red        |
|                5 | blue       |
|                4 | green      |
+------------------+------------+

To get the top 3 but not orange, use this:

SELECT TOP 3 *
FROM colors
WHERE color_name <> 'orange'
ORDER BY rainbow_sequence

That will skip 'orange' and include the 4th item:

+------------------+------------+
| rainbow_sequence | color_name |
+------------------+------------+
|                1 | red        |
|                3 | yellow     |
|                4 | green      |
+------------------+------------+

If but if you change your variable to WHERE color_name <> 'blue' then you get simply the top 3:

+------------------+------------+
| rainbow_sequence | color_name |
+------------------+------------+
|                1 | red        |
|                2 | orange     |
|                3 | yellow     |
+------------------+------------+
  • Perfect! Let's hope my employer doesn't find out stack exchange does a lot of my work for me. – ITM Oct 24 '14 at 16:02
0

You can only write such SQL if you use Personal Geodatabase (ACCESS) and if you open the geodatabase in Access. In this case you can use the IIF function as follows: iif ( condition, value_if_true, value_if_false ) Here is an example.

You can't use full featured SQL using Personal Geodatabases in ArcGIS Desktop, only limited SQL-like expressions to control what data is displayed in the map, etc.

Keywords such as SELECT, ORDER BY, TOP, DISTINCT, etc. are not supported

If you are using a Personal GDB (Access), not File GDB, you can use VBA or create a custom Add-in (.Net) within which you can connect to the underlying Access Database programatically and write and execute such queries (Not Recommend by ESRI). Once you have a recordset you can then iterate through the records and use ObjectIDs and ArcObjects to create a corresponding selection on the map.

What you need to do may be possible to do with ArcGIS for Server (SDE Views?) which functions on top of an actual enterprise DBMS. There may also be a way to use Open source (QGIS or otherwise) with MySQL, PostGres, etc. I can't comment on server based spatial databases because I have only used personal databases with GIS, however some type if an IFF function should be available with most.

  • esri recommends you do not do that. – DPSSpatial Oct 24 '14 at 15:32
  • I know - I also said that in my answer. However, it does work quite well and many do just that and have been doing it since version 8. It's also relatively safe if you do not alter any of the reserved GDB tables. Querying "feature class" tables and even creating joins and saving queries is fine, however accidentally altering or deleting one of the reserved tables will cause problems. Folks go to great lengths do bring real SQL functionality into GIS by creating entire arrays of Access database in custom GIS software. – Jakub Sisak GeoGraphics Oct 24 '14 at 15:37
  • I feel bad for those people... – DPSSpatial Oct 24 '14 at 15:39

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.