[GIS] How to use an IF THEN statement in an sql Select expression

arcgis-desktoppersonal-geodatabaseselectsqlsyntax

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?

Best Answer

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     |
+------------------+------------+
Related Question