【Question】

I have two tables -  Category table and Info table. They are associated through their type fields. I need to join them, group by type and get the latest information according to Info’s create_date field. Can anyone help me out? Thanks a lot.

【Answer】

You can write the query using SQL’s window function or subquery. But the code is difficult to understand, especially when there are more complicated requirements.

Try solving this using esProc SPL (Structured Process Language). It’s easy because SPL supports ordered sets. esProc offers JDBC interface to make it function like a database. Below is the SPL script:

group()@1 directly gets the first member from each group. esProc offers JDBC interface to easily embed the SPL script into a Java application.