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

t3 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.

Sort Pairs

Sort Pairs apply sort orders on different columns within one sort call to reduce computing expense. Using a modified version of the grades table in the previous example, we can create a Sort Pair that arranges the Name column in ascending order, and arranges the Grades column in descending order.

Here's the query:

t4 = grades2.sort(SortPair.ascending("Name"), SortPair.descending("Grades"))

grades2 Name Grade Alyssa 95 Alyssa 97 Alyssa 94 Paul 88 Paul 87 Paul 89 Cathy 92 Cathy 98 Cathy 96

t4 Name Grade Alyssa 97 Alyssa 95 Alyssa 94 Cathy 99 Cathy 96 Cathy 92 Paul 89 Paul 88 Paul 87

The following methods are also available:

• SortPair.ascendingPairs("colA", "colB", "colC") - this will sort all of the specified columns in ascending order
• SortPair.descendingPairs("colA", "colB", "colC") - this will sort all of the specified columns in descending order

Compare the following two queries:

t2 = t.sort(
SortPair.descending("ChangePct"),
SortPair.ascending("Expiry"),
SortPair.descending("OptionVolume")
)

t2 = t.sort(
SortPair.descendingPairs("ChangePct", "OptionVolume"),
SortPair.ascending("Expiry")
)

The results are equivalent, however, the second query is more concise.

Note that the SortPair methods require that the appropriate package be imported into Deephaven. In the following working example drawn from the StockQuotes table in the LearnDeephaven namespace, the Sym column is sorted in descending order, while the Exchange column is sorted in ascending order:

import com.illumon.iris.db.tables.SortPair
StockQuotes = db.t("LearnDeephaven", "StockQuotes").where("Date=`2017-08-25`")
StockQuotes2 = StockQuotes.sort(SortPair.descending("Sym"),SortPair.ascending("Exchange"))

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 LearnDeephaven 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("LearnDeephaven", "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("LearnDeephaven", "StockTrades").where("Date=`2017-08-25`")
tRestrict=t.restrictSortTo("Exchange","Sym")
tUnrestrict = tRestrict.where("Exchange=`Nyse`").updateView("Symbol=Sym").clearSortingRestrictions()
    

---

Last Updated: 28 February 2020 12:20 -05:00 UTC    Deephaven v.1.20200121  (See other versions)

Deephaven Documentation     Copyright 2016-2020  Deephaven Data Labs, LLC     All Rights Reserved