10. Min/Max Expressions

About this task

Purpose

  • Find items that contain specified minimum or maximum criteria
  • Display attribute values for items that contain specified minimum or maximum criteria.

Assumptions

A Parts table has the following values for Order items.

id partno rev
i1 1 5
i1 1 2
i1 2 3
i1 3 1
i2 1 '1
i2 2 2
i2 5 1

Query Expressions

Several types of query expressions can be created using the min/max functionality.

A listitems expression for normal TADB groups.
B listitems expression for list TADB groups.
C getvalues expression for normal TADB groups
D getvalues expression for list TADB groups
  1. listitems expression for normal TADB Groups

listitems "orders","parts.rev=max()"

Where

Criteria Is/are the:
orders item type
Parts Table name
rev Revision number
max() Maximum value

Procedure

  1. The Query expression asks:

    Which Order type item or items has/have the maximum revision number in the Parts table.

  2. The answer is:

    The maximum rev in the table is 5. The Order item returned is i1.

    id partno rev
    i1 1 5
    i1 1 2
    i1 2 3
    i1 3 1
    i2 1 1
    i2 2 2
    i2 5 1
    1. listitems expression for list TADB groups

    listitems "orders","parts(partno='2').min(rev)"

    Where

    Criteria Is/are the:
    orders item type
    Parts Table name
    partno Part number
    2 Part number value to be included in the query.
    min() Minimum value
    rev Revision number
  3. The Query expression asks:

    Which Order items that have a part number of 2 have the minimum revision number?

  4. The answer is:

    i2, part number 2

    Two items have a part number of 2; the  i2 item has the minimum revision number of those part numbers, which is 2.

    id partno rev
    i1 1 5
    i1 1 2
    i1 2 3
    i1 3 1
    i2 1 1
    i2 2 2
    i2 5 1
    1. getvalues expression for normal TADB groups

    getvalues "i1","parts(rev=max()).partno"

    Where

    Criteria Is/are the:
    i1 Part id attribute value
    Parts Table name
    rev Revision number
    max() Maximum value
    partno Part number
  5. The Query expression asks:

    Get the part number of the i1 item that has the maximum rev number.

  6. The answer is:

    1

    partno 1 has the greatest rev for item i1, which is 5.

    id partno rev
    i1 1 5
    i1 1 2
    i1 2 3
    i1 3 1
    i2 1 1
    i2 2 2
    i2 5 1
    1. getvalues expression for list TADB groups

    getvalues "i1","parts(partno='1').min(rev).rev"

    Where

    Criteria Is/are the:
    i1 Part id attribute value
    Parts Table name
    partno Part number
    1 Part number to be queried
    min() Minimum value
    rev Revision number attribute to return
  7. The Query expression asks:

    What is the minimum revision number for i1 items that have a 1 part number.

  8. The answer is:

    2

    Records for item i1 with partno 1 have 2 rev values, 2 and 5.  The minimum of those values is 2.

    id partno rev
    i1 1 5
    i1 1 2
    i1 2 3
    i1 3 1
    i2 1 1
    i2 2 2
    i2 5 1