4.5 Sort records and get desired ones according to the original ordinal numbers
Get the original ordinal numbers of members before they are sorted. One task is to get records of three eldest employees by HIERDATE based on the following employee table.
| ID | NAME | BIRTHDAY | HIREDATE |
|---|---|---|---|
| 1 | Rebecca | 1974-11-20 | 2005-03-11 |
| 2 | Ashley | 1980-07-19 | 2008-03-16 |
| 3 | Rachel | 1970-12-17 | 2010-12-01 |
| 4 | Emily | 1985-03-07 | 2006-08-15 |
| 5 | Ashley | 1975-05-13 | 2004-07-30 |
| … | … | … | … |
SPL offers A.psort() function to get ordinal numbers of members before they are sorted. The function does not change the order of original sequence.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query@x(“select * from EMPLOYEE order by HIREDATE”) |
| 3 | =A2.psort(BIRTHDAY) |
| 4 | =A2(A3.to(3).sort()) |
A1 Connect to the database.
A2 Query employee table and sort records by HIERDATE.
A3 Get ordinal numbers of employee birthdays before sorting.
A4 Get records of three eldest employees from A2’s table sequence according to the three earliest birth dates.
Execution result:
| ID | NAME | BIRTHDAY | HIREDATE |
|---|---|---|---|
| 296 | Olivia | 1968-11-05 | 2006-11-01 |
| 440 | Nicholas | 1968-11-24 | 2008-07-01 |
| 444 | Alexis | 1968-11-12 | 2010-12-01 |
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
