Selecting the MAX value from a feature using ArcObjects?

Question

I have a feature (point) with a field called 'Sequence'. The values in this column range from 1 to n. I can use IQueryFilter to get the first value, 1.

                    Dim pQry As IQueryFilter = New QueryFilter
                    pQry.WhereClause = """Seq"" = 1"
                    pCursor = pFC.Search(pQry, True)
                    If TypeOf pCursor Is IFeatureCursor Then
                        pFeat = pCursor.NextFeature
                        If TypeOf pFeat Is IFeature Then
                            fStartElevation = pFeat.Value(nElevationFld)
                        End If
                    End If

I cannot figure out how to use this method to select the MAX value, since it varies across features. Is it possible? I'm thinking I just need the correct syntax. I have tried a few variations of pQry.WhereClause = """MAX""Seq""" after doing some forum reading regarding aggregate functions in ArcGIS, but I haven't found a solution that works.

If this isn't possible (I figured it should operate similarly to SQL), is there another preferable method for performing this task? I am aware of Select By Attribute, but the syntax for this is tricky (examples online do not work in Arc10) and still requires the attribute table to be read.

EDIT: Here is a working solution using @George's suggestion based on IDataStatistics. I may just turn this into a small function that takes in a Featureclass and field name for the sake of re-usability.

                    pCursor = pFC.Search(Nothing, False)
                    If TypeOf pCursor Is IFeatureCursor Then
                        Dim pData As IDataStatistics = New DataStatisticsClass
                        pData.Field = "Sequence"
                        pData.Cursor = pCursor

                        Dim pStatResults As IStatisticsResults = pData.Statistics
                        Dim intMaxSequence As Integer = pStatResults.Maximum
                    End If

Answer 1

If you are only interested in the MAX value you can use the interface IDataStatistics. Using it you can get a simple statistical result, with min, max, mean, etc.

http://edndoc.esri.com/arcobjects/9.2/ComponentHelp/esriGeoDatabase/IDataStatistics_Example.htm

Answer 2

If you just want the MIN or MAX of a column, the quickest way would be to use the IQueryFilter.PostfixClause to ORDER BY your column ascending or descending then just grab the first row from your cursor. By using IDataStatistics the computer will have to fetch all the rows from the DB, iterate through them and gather stats. Using the ORDER BY we can put all the work on the DB then just fetch a single row over the wire.

IRow queueRow = null;

//use ORDER BY to get the row with the minimum RequestID.
//queueTable is previously opened ITable.

var queryFilter = new QueryFilterClass
{
    SubFields = "*", 
    PostfixClause = string.Format("ORDER BY {0}", requestIdField),
    WhereClause = ""
};

var cursor = queueTable.Search(queryFilter, true);
queueRow = cursor.NextRow();

return queueRow;

Answer 3

You can do this with a subquery in the where clause. e.g on a featureclass named "MyFC" with a field named "ID"

pQry.WhereClause = "ID = (select MAX(ID) from MyFC)"

There are limitations on what you can do with sub queries. See http://desktop.arcgis.com/en/arcmap/10.3/map/working-with-layers/sql-reference-for-query-expressions-used-in-arcgis.htm#GUID-F5320DB5-4E54-4D49-BF31-A58F5379ED9F