MIN

The Min aggregate function allows you to retrieve the smallest value for a set of objects. To use the function, include Min(columnNameInDatabase) into the list of properties requested from the server. For example, the following query retrieves the smallest box office earnings from the Moviesdata table:

DataQueryBuilder dataQueryBuilder = DataQueryBuilder.create().setProperties( "Min(totalBoxOffice)");
Backendless.Data.of( "Movie" ).find( dataQueryBuilder, new AsyncCallback<List<Map>>()
{
  @Override
  public void handleResponse( List<Map> response )
  {
    // print out the first object from the collection
    Log.i( "MYAPP", response.get( 0 ) );
  }

  @Override
  public void handleFault( BackendlessFault fault )
  {
    Log.e( "MYAPP", fault.toString() ); 
  }
});

The query returns the following result, notice the min property in the returned object. The value of the property is the smallest amount in the totalBoxOffice column for all movies in the database:

The function can be applied not only to the columns containing numerical values. For example, the following query retrieves the "smallest" alphanumeric value for the a movie title:

DataQueryBuilder dataQueryBuilder = DataQueryBuilder.create().setProperties( "Min(title)");
Backendless.Data.of( "Movie" ).find( dataQueryBuilder, new AsyncCallback<List<Map>>()
{
  @Override
  public void handleResponse( List<Map> response )
  {
    // print out the first object from the collection
    Log.i( "MYAPP", response.get( 0 ) );
  }

  @Override
  public void handleFault( BackendlessFault fault )
  {
    Log.e( "MYAPP", fault.toString() ); 
  }
});

The smallest value for the alphanumeric columns is determined by the alphabetical order of the characters. As a result, the movie starting with "A" is the one returned by the server:

Using Alias

To change the name of the returned property from min to a custom name, use the following syntax:

Min( columnNameInDatabase ) as customPropertyName

For example, the following query returns the minimum amount in the minimumTotal property:

DataQueryBuilder dataQueryBuilder = DataQueryBuilder.create();
dataQueryBuilder.setProperties( "Min(totalBoxOffice) as minimumTotal" );
Backendless.Data.of( "Movie" ).find( dataQueryBuilder, new AsyncCallback<List<Map>>()
{
  @Override
  public void handleResponse( List<Map> response )
  {
    // print out the first object from the collection
    Log.i( "MYAPP", response.get( 0 ) );
  }

  @Override
  public void handleFault( BackendlessFault fault )
  {
    Log.e( "MYAPP", fault.toString() ); 
  }
});

The response for the query now contains the minimumTotal property:

Grouping Results

Results in the response can be grouped by values from another column. To request grouping of the results, add the groupBy parameter to the request with the value containing the name of the column. For example, the following request returns the minimum earnings value for the movies in the database grouped by the year the movies were released:

DataQueryBuilder dataQueryBuilder = DataQueryBuilder.create();
dataQueryBuilder.setProperties( "Min(totalBoxOffice)", "yearReleased" );
dataQueryBuilder.setGroupBy( "yearReleased" );
Backendless.Data.of( "Movie" ).find( dataQueryBuilder, new AsyncCallback<List<Map>>()
{
  @Override
  public void handleResponse( List<Map> response )
  {
    Log.i( "MYAPP", response );
  }

  @Override
  public void handleFault( BackendlessFault fault )
  {
    Log.e( "MYAPP", fault.toString() ); 
  }
});

The response for this request contains average earning values for the movies grouped by the release year:

Sorting

The results can be sorted using the sortBy parameter. For example, results for the following request will be sorted by the values in the yearReleased column in the descending order:

DataQueryBuilder dataQueryBuilder = DataQueryBuilder.create();
dataQueryBuilder.setProperties( "Min(totalBoxOffice)", "yearReleased" );
dataQueryBuilder.setGroupBy( "yearReleased" );
dataQueryBuilder.setSortBy( "yearReleased DESC" );
Backendless.Data.of( "Movie" ).find( dataQueryBuilder, new AsyncCallback<List<Map>>()
{
  @Override
  public void handleResponse( List<Map> response )
  {
    Log.i( "MYAPP", response );
  }

  @Override
  public void handleFault( BackendlessFault fault )
  {
    Log.e( "MYAPP", fault.toString() ); 
  }
});

To sort results by the aggregated value, assign a custom name to the column and sort by that name:

DataQueryBuilder dataQueryBuilder = DataQueryBuilder.create();
dataQueryBuilder.setProperties( "Min(totalBoxOffice) as minTotal", "yearReleased" );
dataQueryBuilder.setGroupBy( "yearReleased" );
dataQueryBuilder.setSortBy( "minTotal" );
Backendless.Data.of( "Movie" ).find( dataQueryBuilder, new AsyncCallback<List<Map>>()
{
  @Override
  public void handleResponse( List<Map> response )
  {
    Log.i( "MYAPP", response );
  }

  @Override
  public void handleFault( BackendlessFault fault )
  {
    Log.e( "MYAPP", fault.toString() ); 
  }
});

Codeless Reference

Consider the following data table called Movie:

The example below extracts the smallest value from the totalBoxOffice column. To retrieve the smallest value you must use the aggregate function Min(columnName) in the properties argument:

Important

For a detailed description of all input parameters see the Basic Object Retrieval topic of this guide.

After the Codeless logic runs the operation returns an object with the min property containing the value which reflects the smallest of all values stored in the requested column/property:

Using Alias

Any default property returned in the response can have a custom name. You can change the default property name (e.g. min) returned in the response by defining a dynamic property as shown in the excerpt below. The totalBoxOffice is the name of the column in the data table, and the smallestBoxOffice is the custom property name that will be shown in the response instead of the current property name (e.g. min):

Min(totalBoxOffice) as smallestBoxOffice

The example below retrieves the smallest value stored in the "totalBoxOffice" column and returns the result with a custom property name "smallestBoxOffice":

After the Codeless logic runs, the operation returns an object with a new property name smallestBoxOffice:

Grouping The Results

Results can be grouped by values from another column. To group results you have to reference the name of the column in the group by property. By doing so, you instruct the operation to create an object for every group of values retrieved from the column referenced in the group by property.

As you reference the column name in the group by property, the operation groups all values first into separate objects, and then it finds the smallest of all values.

The example below groups values in the totalBoxOffice column by values in the yearReleased column into distinct objects and then finds the smallest value. For better perception of the operation result you can also include the yearReleased column in the properties argument, so that you could see the smallest box office earnings for every year in the response.

First, the operation has grouped all records into distinct objects based on the values in the yearReleased column passed to the group by property. Then for every object the operation has returned the smallest of all values stored in the totalBoxOffice column.

Sorting

You can sort the data by referencing the column name in the sort by property.

Results for the following request will be sorted by the values in the yearReleased column in the descending order:

As you can see, objects returned in the response are sorted in descending order by values in the yearReleased column/property:

The function can be applied not only to the columns containing numerical values. For example, the following query retrieves the "smallest" alphanumeric value for a movie title:

After the Codeless Logic runs, the operation returns an object containing the movieName custom property and the corresponding "smallest" alphanumerical value which is "007 Skyfall".