Skip to main content
Version: 5.x

How-to: GROUP LAST

Example 1

Task

We have a set of books associated with a certain category, and the dates of their receipt.

CLASS Book 'Book';
CLASS Category 'Category';

category 'Category' = DATA Category (Book);
date 'Arrival date' = DATA DATE (Book);

We need to find the latest received book in the selected category.

Solution

book 'Last book' (Category c) = GROUP LAST Book b ORDER date(b), b BY category(b);

It is important to remember that order in the ORDER clause should be uniquely determined. To do this, the book (specifically, its internal ID) should be used as the second parameter since several books may have the same date of receipt.

Example 2

Task

Similar to Example 1, but the author and genre list are specified for each book.

CLASS Author 'Author';
CLASS Genre 'Genre';

author 'Author' = DATA Author (Book);
genre 'Genre' = DATA Genre (Book);
in 'On' = DATA BOOLEAN (Book, Genre);

We need to find the most popular category by author and genre.

Solution

countBooks 'Number of books' (Category c, Author a, Genre g) = GROUP SUM 1 IF in(Book b, g) BY category(b), author(b);

category (Author a, Genre g) = GROUP LAST Category c ORDER countBooks(c, a, g), c WHERE countBooks(c, a, g);

Example 3

Task

We have a certain set of books and the information about price changes per book and warehouse. Each object of the Ledger class reflects a single change in price since a specific date.

CLASS Stock 'Warehouse';

CLASS Ledger 'Price change';
date = DATA DATE (Ledger);
stock = DATA Stock (Ledger);
book = DATA Book (Ledger);

price = DATA NUMERIC[10,2] (Ledger);

We need to identify the current price for a given book at the warehouse.

Solution

currentPrice (Book b, Stock s) = GROUP LAST price(Ledger l) ORDER date(l), l BY book(l), stock(l);//#solution3

Example 4

Task

Similar to Example 3.

We need to find the price valid for a specific date for a book at the warehouse.

Solution

price (Book b, Stock s, DATE d) = GROUP LAST price(Ledger l) ORDER date(l), l WHERE date(l) <= d BY book(l), stock(l);

Example 5

Task

Similar to Example 4, except that a change in price has an expiration date. If this date is not specified, then the price is not limited in time.

dateTo 'Valid until (inclusive)' = DATA DATE (Ledger);

Solution

currentPriceDate (Book b, Stock s) = GROUP LAST price(Ledger l) ORDER date(l), l WHERE NOT dateTo(l) < currentDate() BY book(l), stock(l);
priceDate(Book b, Stock s, DATE d) = GROUP LAST price(Ledger l) ORDER date(l), l WHERE date(l) <= d AND NOT dateTo(l) < d BY book(l), stock(l);

Note that the expression NOT dateTo(l) < date does not always mean the same as dateTo(l) >= date. The difference occurs when the value dateTo(l) equals NULL. In the first case, dateTo(l) < date equals NULL (i. e. false), while NOT NULL equals TRUE. In the second case, the expression obviously equals NULL (i. e. false).