Usually SQL is merely able to sort data by one or more certain fields. When it comes to sorting by a list, the only choice is to use decode or union. But with a long list, the SQL statement will be lengthy. If the items of the list are parameters representing unfixed values, usually a temporary table needs to be created, which makes it harder to perform the sort in SQL. What’s worse, sometimes the list is not equal to the field that needs sorting and their difference is either found or excluded from the sorting result as needed. This is very difficult to be dealt with in SQL.
With support of alignment functions, order-related computations and explicit sets, esProc can easily implement sorting in a fixed order. esProc can operate alone, via the console, as well as through invocation by the reporting tool or Java code. More details are covered by How esProc Assist Writing SQL Queries.
Here are sorting problems concerning a fixed order commonly seen in SQL development, and their solutions in esProc.
Sorting in a simple fixed order
Sort the Service table in which the aNum field will be sorted in an order specified by [1,3,2,4], as shown by selections of source and target tables below:
esProc code:
| A | |
| 1 | $select name,aNum from tb |
| 2 | =A1.align@a([1,3,2,4],aNum).conj() |
A1 performs a SQL statement. The align function groups and sorts records according to the specified list, with the default rule that only the first found record is retained; but with @a, the function will retrieve all records in a group. The conj function concatenates records together.
1. Dynamic sorts can be achieved through a parameter, such as A2=A1.align@a(arg_List, aNum).conj(). So when arg_List is [1,3,2,4], we can get our result. In this way the code becomes reusable.
2. If there are fewer items in the list than the aNum field values, such as arg_List=[1,2,4], records that don’t have matching items will be discarded by default, as shown by the following result:
To append those mismatched records after the sorted ones, use A2=A1.align@n(arg_List, aNum).conj() and the result is as follows:
Or you can use the equal statement – A2=A1.align@s(arg_List, aNum).
3. If the items of the list outnumber the field values, the extra items won’t appear in the sorting result.
Exporting data in specified order
Suppose we need to sort the database table PRODUCT and export the result. Instead of performing the sort by a field, the requirement is that the top N records be sorted in the specified order and the rest by an existing field, as shown in the following selections of source table and target table:
esProc code:
| A | |
| 1 | $select PRODUCT_ID, PRODUCT_NAME from PRODUCT |
| 2 | =A1.align@s(arg_IDList, PRODUCT_ID) |
The align groups and sorts data according to the specified list, and @s adds the mismatched records at the end. The above target table can be obtained when arg_IDList is [300,400,100,200].
Finding missing values
There are a lot of records in Table1 where ID field is a sequence consisting of inconsecutive integers. Now we need to find those missing integers. Below is a selection of the source data:
If the set of integers is small, we can generate a sequence with consecutive numbers in SQL with the minimum and the maximum values, and then compute the difference between this sequence and the ID field values using a subquery. But if the set is big, we need to look for a workaround for implementing the merge algorithm to increase the performance. As a result, the code becomes complicated. esProc provides a direct support of the merge algorithm, and thus can produce the following code:
| 1 | =db.query(“select ID from table1 order by ID”).(ID) |
| 2 | =[to(A1.m(1),A1.m(-1)),A1].merge@d() |
The m function can get members of a set by their sequence numbers in both normal and reverse orders. A1.m(1) an be simplified as A1(1). The to function generates a consecutive sequence. The merge function merges ordered data, and it works with @d to get the difference. Here’s the result:
Intra-group sorting in a fixed order
The attendance table records the attendance information. The requirement is to convert each person’s information per day (a fixed 7 records) to two rows – one is morning and the other is afternoon. For each row, Per_Code, Date, In and Out fields are the same but Break and Return fields are not. Below is the attendance information of a person on a certain day:
According to this data, we should retrieve the 4 records of the morning by the specified sequence numbers and then transform them to a single record with static method. The operation will be performed on records of both morning and afternoon separately. There are a lot of real world cases that involve this kind of order-related algorithm. But as SQL lacks the intrinsic sequence numbers, it turns to pivot and over methods or the like. The problem is the composed code is difficult to understand and debug.
esProc code:
| A | |
| 1 | =$select * from attendance order by Per_Code,Date,Time |
| 2 | =A1.group(Per_Code,Date) |
| 3 | =A2.(~.align([1,7,2,3],#)) |
| 4 | =AM=A3.new(Per_Code, Date, ~.(Time)(1):In ,~.(Time)(2):Out, ~.(Time)(3):Break, ~.(Time)(4):Return) |
| 5 | =A2.(~.align([1,7,5,6],#)) |
| 6 | =PM=A5.new(Per_Code, Date, ~.(Time)(1):In ,~.(Time)(2):Out, ~.(Time)(3):Break, ~.(Time)(4):Return) |
| 7 | =AM | PM |
A3 retrieves record 1, 7, 2 and 3 from each group. A4 joins and converts the four records into a single record and stores it in the empty two-dimensional table sequence AM. ~ represents the current group, # represents a sequence number in a group, and | concatenates records together.
Here’s the result for a person on a certain day:
