"SQL WITH m AS( SELECT *, row_number() OVER(PARTITION BY year(OrderDate),month(OrderDate)) r FROM .."

mars RaqForum 25 No.
194 View • 1 Years ago

2.7 Getting one random record from each group

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(;Amount>35500)
2	=A1.groups(month@y(OrderDate):monthes; CustomerID,OrderDate,Amount,Quantity,EmployeeID,EmployeeName,ShipVia)
3	=A2.run(OrderDate=date@o(OrderDate))