Sort

Sorting is a way to organize and view your data. You can sort any table by clicking on a column header or accessing the column header context menu. Here, we show how to change sort order in a query.

sort()

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

.sort("ColumnsToSortBy")

For example, the following query orders the Exchange column from A-Z.

t=db.t("LearnDeephaven","StockTrades")\
.where("Date = `2017-08-25`)\
.sort("Exchange")

The sort method can also be applied on multiple columns. In this example, we will sort first on the Exchange column and then on the Last column.

Here's the query:

t2=db.t("LearnDeephaven" , "StockTrades")\
.where("Date = `2017-08-25`")\
.sort("Exchange" , "Last")

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

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

In the next example, we'll sort StockTrades in descending order.

Here's the query:

t3 = db.t("LearnDeephaven" , "StockTrades")\
.where("Date = `2017-08-25`")\
.sortDescending("Exchange" , "Last")

SortPair

Sort Pairs apply sort orders on different columns within one sort call to reduce computing expense. Using the StockTrades table once again, we can create a Sort Pair that arranges the Exchange column in ascending order, and arranges the Last column in descending order.

Here's the query:

import com.illumon.iris.db.tables.SortPair

t4 = db.t("LearnDeephaven" , "StockTrades")\
    .where("Date = `2017-08-25`")\
    .sort(SortPair.ascending("Exchange"), SortPair.descending("Last"))

from deephaven import SortPair

t4 = db.t("LearnDeephaven" , "StockTrades")\
    .where("Date = `2017-08-25`")\
    .sort(SortPair.ascending("Exchange"), SortPair.descending("Last"))

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:

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

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

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

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:

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

t5=db.t("LearnDeephaven", "StockTrades").where("Date=`2017-08-25`")\
   .restrictSortTo("Exchange","Sym")

As you can see below, the clicking once on the Exchange column sorts its rows in ascending order, while clicking on the Last column produces no change.

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.

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

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()

Reversing Table Order

Users may want to reverse the order of the rows in an entire table, where the last row becomes the first row and vice versa. Deephaven offers two ways to accomplish this: by using the reverse method in a query, or through the Deephaven console.

The reverse method can be used in a query to reverse an entire Deephaven table. An example of this follows:

t=db.t("LearnDeephaven", "StockTrades").where("Date=`2017-08-25`")
t6=t.reverse()

The following image shows the first 10 rows in the t table:

The next image demonstrates how these rows are now presented as the last ten rows in the t6 table: