This content originally appeared on DEV Community and was authored by Judy
The following PostgreSQL database table is ordered by date field and company field. Some values of column3 are NULL.
Task: Handle records of same company in time order according to the specified rule: First, in each group, delete records until the first non-NULL column3 value appears; then modify each NULL column3 value to its directly previous column3 value until the next non-NULL column3 value appears. Repeat the modification until a new non-NULL column3 value appears, and so on.
Write the following SPL code:
A1: Query the database via JDBC and sort rows by company field and date field.
A2: Handle each record: for records of same group, do not modify the current column3 value if it is non-NULL, and modify it as the directly previous column3 value if it is NULL.
A3: Select records whose column3 value is non-NULL, sort them by date, and return the result.
Read How to Call a SPL Script in Java to find how to integrate SPL into a Java application.
This is one of the problems on StackOverflow. You can click on it to see that the conventional solution is quite complicated, but the SPL approach is really simple and efficient.
This content originally appeared on DEV Community and was authored by Judy
Judy | Sciencx (2024-09-18T08:51:57+00:00) SQL, in each group modify the null value of a specified column as its neighboring value #eg43. Retrieved from https://www.scien.cx/2024/09/18/sql-in-each-group-modify-the-null-value-of-a-specified-column-as-its-neighboring-value-eg43/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.