ORDER_BY Function

Sorts the rows in a PV‑WAVE table variable to create a new table.

Usage

result = ORDER_BY(in_table, 'col_1 [ASC | DESC][, col_2 [ASC | DESC]] ... [, col_n [ASC | DESC]]')

Note:

The entire second parameter is a string and must be enclosed in quotes. Also, note that the vertical bar (|) means “or” in this usage. In this case, you can use either ASC or DESC, but not both.

Input Parameters

in_table—An input PV‑WAVE table variable to be sorted.

col_i—The column(s) on which the sort is to be performed. If more than one column is specified, then result is sorted first by col_1, then col_2, and so on.

ASC—Requires that the rows of the result are sorted in ascending order for that column. If no sort order is specified, ASC is the default.

DESC—Requires that the rows of the result are sorted in descending order for that column.

Returned Value

result—The resulting PV‑WAVE table variable, containing the same rows as in_table, but in the requested sort order.

Keywords

None.

Discussion

The ORDER_BY function produces similar output to the Order By option of the QUERY_TABLE function, but ORDER_BY has a more compact and convenient syntax.

Example

Consider the following table variable (prop_trx), which contains information about property transactions. Property type, location, amount, and transaction date are all recorded.

TRX_ID  PROP_TYPE PROP_XGRID PROP_YGRID TRX_AMT TRX_DATE

   1    2BR_HOUSE 1.45096 2.34159 142166.00 04/20/2005 02:00:37

   2    3BR_CONDO 1.30454 1.12332 108730.00 05/23/2010 11:06:15

   3    4+BR_HOUSE 0.41492 0.67620 273141.00 08/05/1997 06:22:53

   4    4+BR_HOUSE 3.52749 0.91387 267949.00 02/25/2010 06:55:33

   5    2BR_HOUSE 1.56556 0.46332 193346.00 02/22/1997 12:32:35

   6    1BR_HOUSE 2.33187 2.94950 128165.00 04/01/1993 09:46:35

   7    4+BR_HOUSE 1.74596 3.32184 229348.00 05/20/2005 00:15:39

   8    3BR_HOUSE 4.38503 1.13107 203425.00 07/11/2001 11:17:37

   9    4+BR_HOUSE 4.20096 0.90143 376974.00 05/04/1991 23:06:53

   10   STUDIO 0.50743 1.31675 39148.00 05/26/1999 19:01:01

Execute an ORDER_BY function, to sort first by property type, and then by transaction date:

prop_trx = ORDER_BY( prop_trx, 'prop_type, trx_date')

Now, the rows in prop_trx have been reordered:

TRX_ID  PROP_TYPE PROP_XGRID PROP_YGRID TRX_AMT TRX_DATE

6    1BR_HOUSE 2.33187 2.94950 128165.00 04/01/1993 09:46:35

5    2BR_HOUSE 1.56556 0.46332 193346.00 02/22/1997 12:32:35

1    2BR_HOUSE 1.45096 2.34159 142166.00 04/20/2005 02:00:37

2    3BR_CONDO 1.30454 1.12332 108730.00 05/23/2010 11:06:15

8    3BR_HOUSE 4.38503 1.13107 203425.00 07/11/2001 11:17:37

9    4+BR_HOUSE 4.20096 0.90143 376974.00 05/04/1991 23:06:53

3    4+BR_HOUSE 0.41492 0.67620 273141.00 08/05/1997 06:22:53

7    4+BR_HOUSE 1.74596 3.32184 229348.00 05/20/2005 00:15:39

4    4+BR_HOUSE 3.52749 0.91387 267949.00 02/25/2010 06:55:33

10   STUDIO 0.50743 1.31675 39148.00 05/26/1999 19:01:01

Notice that, for properties of the same type, the rows are ordered by ascending date.