Community

Can a SQL expression be created to select the five ( 5 ) greatest and five ( 5 ) least values of a parameter?

2853
3
10-23-2014 09:43 AM
Michael_StanleyGallisdorfer
by
New Contributor
‎10-23-2014 09:43 AM

I wish to select only the five ( 5 ) greatest and five ( 5 ) least values of a parameter ( elevation ) for several sets of over 1000 points. Batch selecting requires that SQL expressions for the desired greatest and least unique values be identified manually, which is very time consuming.

Background:

Five ( 5 ) greatest and least elevation values should be selected from a very large set of elevation points. Setting-up a batch selection with SQL expressions conforming to unique point elevation values consumes much time, slowing data.

Goal:

A SQL expression or tool that allows points with the specified attributes to be selected and exported to a new shapefile. Conceptually, the process might be considered according to the following steps.

1. Sort point elevations, ascending or descending values

2. Identify the greatest and least elevation values

3. Select the greatest, and second-fifth greatest, and the least through fifth-least elevations

4. Export the selected points to a new point shapefile

Logically, the query could be expressed in the following way.

1. The greatest point elevation is greater than all other point elevation values

2. The second greatest point elevation value is less than the point identified in 1, above, but greater than all other values, and so on.

0 Kudos
3 Replies
TedKowal
by
Occasional Contributor III
‎10-23-2014 10:30 AM

The SQL expression to be used would be dependant upon your database.

A SQL Server way of doing would follow this type of logic:

Select Top 5 Min(ElevationFieldName) Elevation, ElevationID from TablewithElevation


Union All


Select Top 5 Max(ElevationFieldName) Elevation, ElevationID from TablewithElevation

(Note that the number of fields and basic data types must be the same in both select statements.)

Oracle does not have a Top function and would have to use embedded select statments with a where clause limiting the rows along with the Union.  (A bit more complicated -- plenty of examples on google)

Access would be similar to SQL Sever

This is what comes immediately to mind!

0 Kudos
Michael_StanleyGallisdorfer
by
New Contributor
‎10-23-2014 11:04 AM

Thanks, Ted.

I am using ArcGIS, although I could probably figure out how to implement your solution using Access.

0 Kudos
TedKowal
by
Occasional Contributor III
‎10-24-2014 07:25 AM

That was my intent ... so give you a starting point. 

One more tip... when using access to develop your queries I would do it in multiple phases so that any errors can be isolated quickly...

Problem set up... if there is no unique unchangeable ID in your GIS data I would create a field in the elevation data (GIS) that is unique and unchangeable.  This field will be used to link the developed sql queries back to the GIS so they may be seen in ArcGIS.

MS Access:

  1. Create a query selecting the Top 5 (individual query) from the GIS data  -- save it in access
  2. Create a query selecting the Bottom 5  (individual query) from the GIS data  -- save it in access
  3. Make sure the above query includes all the same fields including the unchangeable ID field.
  4. Create a Union query combining #1 and #2 -- save it in access

To see the results in ArcGIS, Add the Union Query to the ArcGIS map as a datatable (If you did not know, you can add a named access query to the ArcGIS Map).  Join the elevation gis data with the unique unchangeable ID with that same ID in the union query with matching ID's only.  The result map should now only show the Top 5 and Bottom 5 results.

Hope this is helpful,

Ted

0 Kudos