AnsweredAssumed Answered

Help with SQL expression

Question asked by drewlindow on Jan 27, 2014
Latest reply on Jan 28, 2014 by rfairhur24

Like • Show 0 Likes0
Comment • 3

I have a dataset of end points of some stream lines. I want to select the points that are lowest in elevation out of each set of endpoints.

The attributes of the point dataset include "elevation" of each point and a number "stream" representing the stream it came from. So I'm looking for something like: select the point with minimum "elevation" from each pair of points that have the same "stream" value

rfairhur24 Jan 28, 2014 12:40 PM

Correct Answer

Ok I think I'm getting closer but still not there yet. I will need to use some sort of subquery in order to include a GROUP BY statement and the min() function. Here is what I have now but there are a few problems:
SELECT * FROM endpoints WHERE  elevation in (SELECT min(elevation) FROM endpoints GROUP BY streamID)
Problem 1: using the in operator is a problem because if I have two points with the same elevation value and one of them is the minimum of a stream it will return both of them even if the other is not the minimum of its stream.

Problem 2: when I try and run this statement I get an error: "An invalid SQL statement was used. [endpoints]". Am I not entering the table name correctly? I also tried surrounding it in double quotes but no change.

Thanks for helping.

If this is an Access database or an SDE database you might get it to work, but you need to look at the help specific to that database and get it to work within the origin program first. I am not sure that even they support such queries without a separate external table holding the min values for each group. (I.e., in Access I have to do a Make Table Query base on the Min of each group and then run the query against the main table for this to work).

If this is a shapefile or a File Geodatabase, grouped subqueries that respect each group are not supported, since as you said the group by operator will always find the list of every elevation that is a min of any of the groups and then the selection will match any elevation in that list regardless of whether it is in correct the group or not. To do this efficiently programatically you need a cursor and a dictionary in Python to maintain a list that pairs the correct unique streamID with each min elevation for that group, or you need to run Summary Statistics and create a Summary Table with the min elevation for each unique streamID case and join it to your full table on the streamID to be able to select min elevations that respect a group. This information came from the programmer who wrote the Shapefile and File Geodatabase SQL specifications.

See the reply in context

No one else had this question

Outcomes

AngelynDavis Jan 28, 2014 6:46 AM

Disclaimer: Not a SQL guru, just play with it a bit. Replace your my field/table names with the appropriate names from your data.

Is your elevation in numeric format? If it is text, you will need to convert it to a number for minimum to work.

Try:

Select elevation, stream_id from StreamTable
where minimum(elevation)
group by stream_id

You might need to switch around line 2 and 3.
You can play around with that until someone with more SQL expertise replies.

Actions

drewlindow Jan 28, 2014 11:48 AM

Ok I think I'm getting closer but still not there yet. I will need to use some sort of subquery in order to include a GROUP BY statement and the min() function. Here is what I have now but there are a few problems:

SELECT * FROM endpoints WHERE

elevation in (SELECT min(elevation) FROM endpoints GROUP BY streamID)

Problem 1: using the in operator is a problem because if I have two points with the same elevation value and one of them is the minimum of a stream it will return both of them even if the other is not the minimum of its stream.

Problem 2: when I try and run this statement I get an error: "An invalid SQL statement was used. [endpoints]". Am I not entering the table name correctly? I also tried surrounding it in double quotes but no change.

Thanks for helping.

Actions

rfairhur24 Jan 28, 2014 12:40 PM

Ok I think I'm getting closer but still not there yet. I will need to use some sort of subquery in order to include a GROUP BY statement and the min() function. Here is what I have now but there are a few problems:
SELECT * FROM endpoints WHERE  elevation in (SELECT min(elevation) FROM endpoints GROUP BY streamID)
Problem 1: using the in operator is a problem because if I have two points with the same elevation value and one of them is the minimum of a stream it will return both of them even if the other is not the minimum of its stream.

Problem 2: when I try and run this statement I get an error: "An invalid SQL statement was used. [endpoints]". Am I not entering the table name correctly? I also tried surrounding it in double quotes but no change.

Thanks for helping.

Actions

3 Replies

AngelynDavis Jan 28, 2014 6:46 AM
Mark Correct
Correct Answer
Disclaimer: Not a SQL guru, just play with it a bit. Replace your my field/table names with the appropriate names from your data.

Is your elevation in numeric format? If it is text, you will need to convert it to a number for minimum to work.

Try:

Select elevation, stream_id from StreamTable
where minimum(elevation)
group by stream_id

You might need to switch around line 2 and 3.
You can play around with that until someone with more SQL expertise replies.
Like • Show 0 Likes0
Actions
drewlindow Jan 28, 2014 11:48 AM
Mark Correct
Correct Answer
Ok I think I'm getting closer but still not there yet. I will need to use some sort of subquery in order to include a GROUP BY statement and the min() function. Here is what I have now but there are a few problems:
```
SELECT * FROM endpoints WHERE

elevation in (SELECT min(elevation) FROM endpoints GROUP BY streamID)
```
Problem 1: using the in operator is a problem because if I have two points with the same elevation value and one of them is the minimum of a stream it will return both of them even if the other is not the minimum of its stream.

Problem 2: when I try and run this statement I get an error: "An invalid SQL statement was used. [endpoints]". Am I not entering the table name correctly? I also tried surrounding it in double quotes but no change.

Thanks for helping.
Like • Show 0 Likes0
Actions
rfairhur24 Jan 28, 2014 12:40 PM
Unmark Correct
Correct Answer
Ok I think I'm getting closer but still not there yet. I will need to use some sort of subquery in order to include a GROUP BY statement and the min() function. Here is what I have now but there are a few problems:

SELECT * FROM endpoints WHERE elevation in (SELECT min(elevation) FROM endpoints GROUP BY streamID)

Problem 1: using the in operator is a problem because if I have two points with the same elevation value and one of them is the minimum of a stream it will return both of them even if the other is not the minimum of its stream.

Problem 2: when I try and run this statement I get an error: "An invalid SQL statement was used. [endpoints]". Am I not entering the table name correctly? I also tried surrounding it in double quotes but no change.

Thanks for helping.
If this is an Access database or an SDE database you might get it to work, but you need to look at the help specific to that database and get it to work within the origin program first. I am not sure that even they support such queries without a separate external table holding the min values for each group. (I.e., in Access I have to do a Make Table Query base on the Min of each group and then run the query against the main table for this to work).

If this is a shapefile or a File Geodatabase, grouped subqueries that respect each group are not supported, since as you said the group by operator will always find the list of every elevation that is a min of any of the groups and then the selection will match any elevation in that list regardless of whether it is in correct the group or not. To do this efficiently programatically you need a cursor and a dictionary in Python to maintain a list that pairs the correct unique streamID with each min elevation for that group, or you need to run Summary Statistics and create a Summary Table with the min elevation for each unique streamID case and join it to your full table on the streamID to be able to select min elevations that respect a group. This information came from the programmer who wrote the Shapefile and File Geodatabase SQL specifications.
Like • Show 0 Likes0
Actions

GeoNet

The Esri Community

Help with SQL expression

Outcomes

Related Content

Recommended Content