RaqForum 25 No.
315 View  • 
     ...

2.7 Getting the first/last record from each group

 
performance optimization practice(38)

SQL

WITH m AS(
    SELECT *, row_number() OVER(PARTITION BY year(OrderDate),month(OrderDate)) r 
    FROM Orders
    WHERE Amount>35500)
SELECT * 
FROM m 
WHERE r=1

SPL

A
1 =file(“Orders.ctx”).open().cursor@x(;Amount>35500)
2 =A1.groups(month@y(OrderDate):monthes;top(1;0) :tmp).conj(tmp)
3 =A2.run(OrderDate=date@o(OrderDate))

A2 When the 2nd parameter in top(1;0) is a constant (usually 0), it returns the first record of each group. To return the last record of each group, use top(-1;0).

performance optimization practice(38)
 
 •  315 View  •