Sorting

Along with filtering, sorting is a way to organize and view your data. All tables generated by Deephaven are already ordered. However, you may want to change the sort order in a query. This can be accomplished using the sort or sortDescending methods.

	Watch the video below titled, "Sorting Data with Queries"

sort

Syntax

sort("ColumnsToSortBy")

The sort method rearranges rows in a table by ascending (smallest to largest) order based on the column(s) listed in the columnsToSortBy argument.

Let's look at an example using the following table. Our goal for this example is to sort the rows in the grades table in ascending order based on the values in Name column.

Here's the query:

t=grades.sort("Name")

A new variable named t is created to hold the new table. grades is the name of the source table. sort is the method and the column we want to base the sort on is Name.

When this query runs, the following table is generated with rows rearranged based on the ascending order of data in the Name column.

grades

Name	Grade1	Grade2	Grade3
Alyssa	95	97	94
Bob	96	86	82
Paul	88	87	89
Cathy	92	98	96
Albert	96	99	93

t

Name	Grade1	Grade2	Grade3
Albert	96	99	93
Alyssa	95	97	94
Bob	96	86	82
Cathy	92	98	96
Paul	88	87	89

The sort method can also be applied on multiple columns. All we need to do is add another argument. In this example, we will sort first on the Grade1 column and then on the Grade2 column.

Here's the query:

t2=grades.sort("Grade1", "Grade2")

grades

Name	Grade1	Grade2	Grade3
Alyssa	95	97	94
Bob	96	86	82
Paul	88	87	89
Cathy	92	98	96
Albert	96	99	93

t2

Name	Grade1	Grade2	Grade3
Paul	88	87	89
Cathy	92	98	96
Alyssa	95	97	94
Bob	96	86	82
Albert	96	99	93

The new t2 table has been reordered so the rows are now sorted in ascending order based on the values in the Grade1 and Grade2 columns.

sortDescending

Syntax

sortDescending("ColumnsToSortBy")

The sortDescending method rearranges rows in a table by descending (largest to smallest) order based on the column(s) listed in the columnsToSortBy argument.

Using the same grades table as before, we can sort the rows using the sortDescending method on the Grade1 and Grade2 columns.

Here's the query:

t3=grades.sortDescending("Grade1", "Grade2")

grades

Name	Grade1	Grade2	Grade3
Alyssa	95	97	94
Bob	96	86	82
Paul	88	87	89
Cathy	92	98	96
Albert	96	99	93

Name	Grade1	Grade2	Grade3
Albert	96	99	93
Bob	96	86	82
Alyssa	95	97	94
Cathy	92	98	96
Paul	88	87	89

The new t3 table has been reordered so the rows are sorted in descending order based on the values in the Grade1 and Grade2 columns.

Restricting Table Sorting

Sorting can be a very expensive operation in database applications in terms of processing power, time, and memory, especially for tables with millions or billions of rows. When many users access and sort the same large table at the same time, the efficiency of the system can be compromised, resulting in slow response times or program failure. Therefore, it can become necessary to add sorting restrictions to such tables, or to limit access to the table until its size has been reduced.

Sorting restrictions can be applied to tables by using the restrictSortTo method.

Syntax

restrictSortTo("<Col1>", "<Col2>",...)

The arguments to the restrictSortTo method are the name(s) of the columns in the table where sorting will be allowed. Sorting in any other columns in the table will not be available.

For example, the following example adds sorting restrictions to the StockTrades table in the LearnIris namespace. The query first filters the table to a single day with the where operation, and then restricts sorting to the Exchange and Sym columns only.

t=db.t("LearnIris", "StockTrades").where("Date=`2017-08-25`")
t2=t.restrictSortTo("Exchange","Sym")

Sorting restrictions are transitive, meaning the restrictions continue to be enforced as subsequent tables are generated. However, new table objects are not created when using the restrictSortTo operation. To create a new table object after applying sort restrictions, you must assign a new variable to hold the new table object and apply an operation such as select, view, updateView, or where. Alternatively, you can also wait to apply the sorting restrictions until the end of the query.

Removing Sorting Restrictions

Sorting restrictions can be removed from a table using the clearSortingRestrictions method.

Syntax

clearSortingRestrictions()

Using this method removes all sorting restrictions for the table. For example, the following query first restricts sorting in the table named tRestrict, and then removes all sorting restrictions in the table named tUnrestrict:

t=db.t("LearnIris", "StockTrades").where("Date=`2017-08-25`")
tRestrict=t.restrictSortTo("Exchange","Sym")
tUnrestrict = tRestrict.where("Exchange=`Nyse`").updateView("Symbol=Sym").clearSortingRestrictions()

Last Updated: 23 September 2019 12:17 -04:00 UTC Deephaven v.1.20181212 (See other versions)