Talend Sort Row Data

The Talend Sort row or tSortRow field is useful for sorting the input records in ascending or descending order and returning the output. We can use this Sort Row field to see the sales from high to low, etc.

In this Talend Sort row example, we used the Customer table in our SQL Server database. The following image shows the data inside that table.

Customer Sales Table with HireDate

If you select the tSortRow in the palette, you can see a little tooltip explaining the field. The Talend tSortRow sorts the data flows. It allows advanced sorting options such as multi-column sorting, ascending, descending sorting, and alphabetical sorting.

First, drag and drop the DBConnection and DBCommit to establish a connection to the SQL Server, and to close it. Second, use the DBInput, choose the existing Connection option, and select the tDBConnection. Next, select the schema from the Talend Repository because all our table schemas are available in the Repository.

Next, click on the browse button beside the Edit Schema label, and select the Customers table from the Table Schemas Metadata.

Talend Sort Row Example

Now, you can see the Customer table as the Table Name. Next, click the Guess Query button so that Talend will automatically write a Select Statement query for you.

Configure Server Connection and write table Select query

Next, drag and drop the Talend tSortRow from the palette to the job design.

Add Talend tSortRow to Workflow for Sort Data

Please connect the DBInput main row to Talend tSortRow. Mostly, the input columns will sync with tSortRow. If that is not the case, then click on the Sync columns button.

Click Talend tSortRow Edit Schema to Sort Data

Next, click the Edit Schema button to check or alter the tSortRow schema as you need.

Check the Columns in Edit Schema

Within the Talend Sort Row Component tab, there is the Criteria section. It is a place where we select the column and specify the sorting order.

Please click on the Add (plus) button. By default, it adds the first column, i.e., EmpID from the Customers table.

Add Column and ASC or DESC in Talend tSortRow to Sort Data

Please use the drop-down box under the Schema Column to select the required column. Here, we chose the Occupation field.

Under the sort num or alpha, we have the following options:

  • num: Use this for sorting numeric values or numbers.
  • alpha: To sort the string data or alphabets.
  • date: Use this sorting option for sorting date values.

Since Occupation has string data, we selected the alpha. Next, we can keep the order asc or desc option, and by default, asc. Otherwise, you can choose the desc.

Configure the Columns, Order of the Talend tSortRow to Sort Data

Next, we are using the DBOutput to save the Talend tSortRow output in a SQL Database. Here, we are creating the Talend_Sorted_Customers table as the destination table. Let us run the Talend Sort Row Job.

Run the Job

From the above screenshot, you can see an error stating that EmpID is an identity column. But we are inserting records into it. To resolve this, please go to the components tab.

Please check the Turn on Identity Insert option and Specify the identity field option. Next, select the Identity field and specify the start value and Step. By default, it will choose the integer field and assign one as Start value and Step. However, you can change it as per your requirements.

Configure tDBOutput

Now, you can see that the Talend sort row data job has run successfully!

Run Talend tSortRow to Sort Data

Let me open the Management studio to check the Sort row result. Remember, Talend tSortRow is the same as the SQL Order By Clause.

Result Table

This time, we are using two fields and running the job. It means the tSortRow has to sort the Customers by Occupation in ascending order and then sort by Yearly Income in descending order.

Talend tSortRow to Sort Data using multiple columns

Please check the Talend Sort row job result.

Result Set

Here, we used another column HireDate to sort the date field in descending order.

tSortRow to Sort Data in Descending Order

Please check the tSortRow result.

Descending Final Table