Get the First from Each Group
【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:
| A |
|
| 1 |
$(db1)select * from Info by create_date |
| 2 |
=A1.group@1(type) |
group()@1 directly gets the first member from each group. esProc offers JDBC interface to easily embed the SPL script into a Java application.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
