# VECTOR_SORT

The `VECTOR_SORT` function sorts the elements in the vector expression and returns the sorted vector.

## Syntax

`VECTOR_SORT(vector_expression [,'ASC'|'DESC'])`

## Arguments

• `vector_expression`: An expression that evaluates to a vector. Vectors can be stored in SingleStore using the native `VECTOR` type (Vector Type) or the `BLOB` type (BLOB Types). SingleStore recommends using the `VECTOR` type when possible.

• `ASC` or `DESC`: The sort order in which the vector will be returned. `ASC` means ascending order. `DESC` means descending order. `ASC` is the default.

## Return Type

The function returns the type of the first argument (the vector_expression).

## Using VECTOR_ELEMENTS_SORT with the VECTOR Type

The examples below show two uses of `VECTOR_ELEMENTS_SORT` with different types of `VECTOR` arguments.

### Example 1 - Vector with F32 Element Type

First create a table of vectors of length 4 using the `VECTOR` type and insert data into that table.

```CREATE TABLE vectors (id int, vec VECTOR(4) not null);
INSERT INTO vectors VALUES (1, '[0.45, 0.55, 0.495, 0.5]');INSERT INTO vectors VALUES (2, '[0.1, 0.8, 0.2, 0.555]');INSERT INTO vectors VALUES (3, '[-0.5, -0.03, -0.1, 0.86]');INSERT INTO vectors VALUES (4, '[0.5, 0.3, 0.807, 0.1]');```

Note

The default element type for Vector Type is 32-bit floating point (`F32`). The Vector Type supports other element types, including integer types, as described in the Vector Type documentation.

The following SQL sorts each of those vectors in ascending (`ASC`) order.

Click the Playground icon to the right of the SQL listing to try this query. Also, try experimenting and writing your own query.

`SELECT id, VECTOR_SORT(vec, 'ASC')FROM vectorsORDER BY id;`
``````+------+---------------------------------------------------+
| id   | VECTOR_SORT(vec, 'ASC')                           |
+------+---------------------------------------------------+
|    1 | [0.449999988,0.495000005,0.5,0.550000012]         |
|    2 | [0.100000001,0.200000003,0.555000007,0.800000012] |
|    3 | [-0.5,-0.100000001,-0.0299999993,0.860000014]     |
|    4 | [0.100000001,0.300000012,0.5,0.806999981]         |
+------+---------------------------------------------------+``````

### Example 2 - Vector with I16 Element Type

This example shows how to use `VECTOR_SORT` with a `VECTOR` with an elementType of 16-bit integer (`I16`).

```CREATE TABLE vectors_i16(id INT, vec VECTOR(3, I16));
INSERT INTO vectors_i16 VALUES(1, '[1, 2, 3]');INSERT INTO vectors_i16 VALUES(2, '[4, 5, 6]');INSERT INTO vectors_i16 VALUES(3, '[1, 4, 8]');```

The following SQL sorts each of the vectors in the `vectors_i16` table in descending order.

`SELECT id, VECTOR_SORT(vec, 'DESC')FROM vectors_i16ORDER BY id;`
``````+------+--------------------------+
| id   | VECTOR_SORT(vec, 'DESC') |
+------+--------------------------+
|    1 | [3,2,1]                  |
|    2 | [6,5,4]                  |
|    3 | [8,4,1]                  |
+------+--------------------------+``````

## Using VECTOR_SORT with Vectors as BLOBs

The following example shows the use of `VECTOR_SORT` to sort the elements of a vector stored as a `BLOB`.

### Example 1 - BLOB Argument

Create a table with a column of type `BLOB` to store the vectors. The second column in this table, with column name `vec` and type `BLOB`, will store the vectors. This example demonstrates storing vector data using `BLOB`s, hence the column of type `BLOB` named `vec`.

Then use the `JSON_ARRAY_PACK` built-in function to easily insert properly formatted vectors.

```CREATE TABLE vectors_b (id int, vec BLOB not null);
INSERT INTO vectors_b VALUES (1, JSON_ARRAY_PACK('[0.1, 0.8, 0.2, 0.555]')); INSERT INTO vectors_b VALUES (2, JSON_ARRAY_PACK('[0.45, 0.55, 0.495, 0.5]'));```

The following query sorts the vectors in the `vectors_b` table in ascending order and uses `JSON_ARRAY_UNPACK` to see the results in JSON format.

`SELECT id, JSON_ARRAY_UNPACK(VECTOR_SORT(vec)) AS vector_sortFROM vectors_bORDER BY id;`
``````+------+---------------------------------------------------+
| id   | vector_sort                                       |
+------+---------------------------------------------------+
|    1 | [0.100000001,0.200000003,0.555000007,0.800000012] |
|    2 | [0.449999988,0.495000005,0.5,0.550000012]         |
+------+---------------------------------------------------+``````

The results are sorted in ascending order as that is the default sort order for `VECTOR_SORT`.

`JSON_ARRAY_UNPACK` was used here to output the vectors in readable format because `VECTOR_SORT` returns a `BLOB` when its second argument is a `BLOB`.

The following query sorts the vectors in the `vectors_b` table and uses `HEX` to view the results in hexadecimal format.

`SELECT id, HEX(VECTOR_SORT(vec)) as vector_sortFROM vectors_bORDER BY id;`
``````+------+----------------------------------+
| id   | vector_sort                      |
+------+----------------------------------+
|    1 | CDCCCC3DCDCC4C3E7B140E3FCDCC4C3F |
|    2 | 6666E63EA470FD3E0000003FCDCC0C3F |
+------+----------------------------------+``````

### Using Suffixes for Other Element Types with BLOBs

The default element type for vector storage and processing is 32-bit floating point (`F32`). However, other element types are supported.

You can specify the datatype of the vector elements to be used in the operation by adding a suffix to the function. All operations are done using the specified datatype. Omitting the suffix from the function is equivalent to suffixing it with `_F32`.

When using a suffix, the return type will be the type of the suffix.

Note

The functions with suffixes do not work with the `VECTOR` type.

The following table lists the suffixes and their data type.

Suffix

Data Type

`_I8`

8-bit signed integer

`_I16`

16-bit signed integer

`_I32`

32-bit signed integer

`_I64`

64-bit signed integer

`_F32`

32-bit floating-point number (IEEE standard format)

`_F64`

64-bit floating-point number (IEEE standard format)

### Example 2 - BLOBs with 16-bit Integers

Below is an example of using `JSON_ARRAY_PACK` and `VECTOR_SORT` with 16-bit signed integers.

First create a table of vectors stored as 16-bit integers. Note the use of the `_I16` suffix on `JSON_ARRAY_PACK`.

```CREATE TABLE vectors_b_i (id int, vec BLOB not null);
INSERT INTO vectors_b_i VALUES (1, JSON_ARRAY_PACK_I16('[1, 3, 2, 5]')); INSERT INTO vectors_b_i VALUES(2, JSON_ARRAY_PACK_I16('[23, 4, 1, 8]'));```

The following query sorts the vectors in the `vectors_b_i` table in ascending order.

`SELECT id, JSON_ARRAY_UNPACK_I16(VECTOR_SORT_I16(vec)) AS vector_sortFROM vectors_b_iORDER BY id;`
``````+------+-------------+
| id   | vector_sort |
+------+-------------+
|    1 | [1,4,8,16]  |
|    2 | [2,5,10,15] |
+------+-------------+``````

Note

Be sure that the suffixes you use to pack the vector data match the suffixes you use to unpack the data and the suffixes you use on functions to process that data.

### Formatting Binary Vector Data for BLOBs

When using the `BLOB` type for vector operations, vector data can be formatted using `JSON_ARRAY_PACK`. If your vector data is already in a packed binary format, you can load that data into the `BLOB`s. The data must be encoded as a `BLOB` containing packed numbers in little-endian byte order. Vectors stored as `BLOB`s can be of any length; however, the input blob length must be divisible by the size of the packed vector elements (1, 2, 4 , or 8 bytes, depending on the vector element).