BigMemory 4.3.7 | Product Documentation | BigMemory Max Developer Guide | Searching with BigMemory SQL | SQL Syntax and Examples | SELECT Clause
 
SELECT Clause
This section provides a detailed specification for the SELECT clause. The general form of SELECT statement is:
SELECT [ * | KEY | VALUE |
[ (attribute 1, attribute 2, ... , attribute N) | aggregatorFunction(attribute) ] ]
[ FROM cache]
[ WHERE condition ]
[ GROUP BY attribute]
[ ORDER BY {attribute}]
[ LIMIT { count }]
The SELECT clause defines the Attributes to be selected.
Note:
Attributes are specified by their names and must match the Search configuration. Otherwise, an exception is thrown.
You can also select the cache key and value denoted by the keywords key and value. A SELECT clause can have Attributes, keys, and values, in any order. To return all searchable attributes, use the wildcard character *.
The name of the cache to be queried is specified using the FROM clause.
// get all attributes for a person named Dave
select * from Person where name = 'Dave'
//get only the key
select key from Person where name = 'Dave'
//get only the value
select value from Person where name = 'Dave'
// get key, value and all attributes for a person named Dave
select *, key, value from Person where name = 'Dave'
// get only the age for a person named Dave
select age from Person where name = 'Dave'
// get both age and zip
select age, zip from Person where name = 'Dave'
Aggregator Functions
Aggregator functions can be used to perform calculations on a specified attribute's values. The following functions are available:
*sum
*max
*min
*average (can be 'average' or 'avg')
*count
// get the average age for all persons older than 30
select avg(age) from Person where age > 30
Aggregation Examples
select key,sum(age) from Person
select key,average(age) from Person where age > 10
select key,sum(age),min(age) from Person where age > 10