RaqForum 25 No.
270 View  • 
     ...

2.3 Getting record(s) containing the max/min value

 
performance optimization practice(38)

Find the order record containing the largest amount:
SQL

WITH m AS (
    SELECT max(Amount) mta 
    FROM Orders
    WHERE OrderDate>='2022-01-01') 
SELECT Orders.CustomerID,OrderDate,Amount, 
       Quantity, EmployeeID,EmployeeName,ShipVia
FROM m,Orders
WHERE m.mta=Orders.Amount and OrderDate>='2022-01-01'

SPL

A
1 >st=date(“2022-01-01”), start=days@o(st)
2 =file(“Orders.ctx”).open().cursor@x(;OrderDate>=start)
3 =A2.total(maxp@a(Amount))

A3 maxp@a(Amount) gets all records containing the largest Amount value. It is possible that there are multiple eligible order records, and @a option enables getting them all to return.

Find the last order each salesperson signs:
SQL

WITH m AS (
    SELECT EmployeeID,max(OrderDate) md
    FROM Orders
    WHERE Amount>1000
    GROUP BY EmployeeID) 
SELECT Orders.CustomerID,OrderDate,Amount, 
    Quantity, Orders.EmployeeID,EmployeeName,ShipVia
FROM m, Orders
WHERE m.EmployeeID =Orders.EmployeeID and m.md=Orders.OrderDate and Amount>1000

SPL

A
1 =file(“Orders.ctx”).open().cursor@x(;Amount>1000)
2 =A1.groups(EmployeeID; maxp(OrderDate)).(#2)
3 =A2.run(OrderDate=date@o(OrderDate))

A2 Here we assume that the last order each salesperson signs is unique, and do not add the @a option but just use maxp to return the single record containing the maximum value.

We can also use top function to get the target record:

A
2 =A1.groups(EmployeeID; top@1(1;-OrderDate)).(#2)

A2 top@1(1;-OrderDate) gets the record containing the largest OrderDate value. Without the negative sign the function gets the record containing the smallest OrderDate value.
 

performance optimization practice(38)
 
 •  270 View  •