【Question】

I need a query to retrieve data select * from table where plan IN(1,2,3,4) group by plan order by id limit 4.

My problem is if the table has at least one row in each plan then its returning 4 rows for each plan. if the table has no row in any of the plan(i.e say plan 4 has no rows in table) then its returning 3 rows. but i need 4 rows in the order(plan in 1,2,3,4).

【Answer】

To align a result set by a specified sequence, SQL needs to compose a query using JOIN【select numbers.val,plantest.id from plantest right join numbers on numbers.val = plantest.plan group by plan order by id】. Either a table, like numbers here, is needed to store values 1, 2, 3, 4, or a sequence number table needs to be syntactically generated. Both are complicated.

SPL offers align() function to do this conveniently.

To query a table grouped by plan=1,2,3,4 and display results no matter whether the table has rows in any of the plan.

Below is esProc SPL code:

A1: Connect to the database;

A2: Get records where plan is 1,2, 3, and 4 from plantest table grouped by plan, sort them by id, and return the first record from each group.

A3: align() function aligns A2’s record sequence to another sequence. Parameter to(4) is the referencing sequence by which the alignment is performed. Parameter plan is the alignment expression. A2’s record sequence is aligned with a sequence of plan values - [1,2,3,4], which can be written as to(4). The aligning result is what we want where nulls are displayed.

A2

A3