How to Implement Data Sorting in Retool Tables

1// Alternative: set default sort via table1.setSort() in a Page Load query
2// (JS Query with 'Run this query on page load' enabled)
3await table1.setSort([{ columnId: 'created_at', desc: true }]);
4
5// Multiple default sorts (multi-column):
6await table1.setSort([
7  { columnId: 'department', desc: false },
8  { columnId: 'last_name', desc: false }
9]);

1// In a JS Query or event handler:
2// Sort by created_at descending (newest first):
3await table1.setSort([{ columnId: 'created_at', desc: true }]);
4
5// Sort by last_name ascending (A-Z):
6await table1.setSort([{ columnId: 'last_name', desc: false }]);
7
8// Reset sort (no sort applied):
9await table1.setSort([]);
10
11// Toggle sort direction based on current state:
12const currentSort = table1.sort;
13const isDesc = currentSort?.[0]?.desc ?? false;
14await table1.setSort([{ columnId: 'created_at', desc: !isDesc }]);
15
16// Note: table1.setSort() is async — await it before reading table1.sort

1// Multi-column sort: department ascending, then last_name ascending:
2await table1.setSort([
3  { columnId: 'department', desc: false },
4  { columnId: 'last_name', desc: false },
5  { columnId: 'first_name', desc: false }
6]);
7
8// Business-logic sort: priority descending, then due_date ascending:
9await table1.setSort([
10  { columnId: 'priority', desc: true },
11  { columnId: 'due_date', desc: false }
12]);

1-- SQL Query with server-side sorting:
2-- IMPORTANT: Column names cannot be parameterized in SQL,
3-- so validate using a whitelist in a transformer first.
4
5-- In the query, use a CASE expression for safe dynamic sorting:
6SELECT id, first_name, last_name, email, created_at, revenue, status
7FROM users
8WHERE
9  ({{ searchInput.value }} = '' OR
10   first_name ILIKE {{ '%' + searchInput.value + '%' }})
11ORDER BY
12  CASE
13    WHEN {{ table1.sortColumn || 'created_at' }} = 'first_name' THEN first_name
14    WHEN {{ table1.sortColumn || 'created_at' }} = 'last_name' THEN last_name
15    WHEN {{ table1.sortColumn || 'created_at' }} = 'email' THEN email
16    WHEN {{ table1.sortColumn || 'created_at' }} = 'status' THEN status
17    ELSE NULL
18  END
19  {{ table1.sortDirection === 'descend' ? 'DESC' : 'ASC' }},
20  CASE
21    WHEN {{ table1.sortColumn || 'created_at' }} IN ('revenue', 'created_at')
22    THEN NULL ELSE NULL
23  END,
24  -- Numeric/date sorts need separate CASE:
25  CASE
26    WHEN {{ table1.sortColumn || 'created_at' }} = 'revenue'
27    THEN revenue END
28  {{ table1.sortDirection === 'descend' ? 'DESC' : 'ASC' }},
29  CASE
30    WHEN {{ table1.sortColumn || 'created_at' }} = 'created_at'
31    THEN created_at END
32  {{ table1.sortDirection === 'descend' ? 'DESC' : 'ASC' }}
33LIMIT {{ table1.pageSize || 50 }}
34OFFSET {{ (table1.page - 1) * (table1.pageSize || 50) || 0 }};

1// Select component options (static):
2// [
3//   { label: 'Newest First', value: 'created_at_desc' },
4//   { label: 'Oldest First', value: 'created_at_asc' },
5//   { label: 'Name A-Z', value: 'last_name_asc' },
6//   { label: 'Revenue High-Low', value: 'revenue_desc' }
7// ]
8
9// JS Query: applySortFromSelect
10const sortMap = {
11  created_at_desc: { columnId: 'created_at', desc: true },
12  created_at_asc: { columnId: 'created_at', desc: false },
13  last_name_asc: { columnId: 'last_name', desc: false },
14  revenue_desc: { columnId: 'revenue', desc: true }
15};
16
17const selectedSort = sortSelect.value;
18if (sortMap[selectedSort]) {
19  await table1.setSort([sortMap[selectedSort]]);
20}

1// In a text component (shows active sort state):
2{{ 'Sorted by: ' + (table1.sortColumn || 'default') + ' ' + (table1.sortDirection || '') }}
3
4// In a JS Query reading current sort:
5const currentSort = table1.sort; // array of {columnId, desc}
6const columnId = table1.sortColumn; // 'created_at'
7const direction = table1.sortDirection; // 'ascend' or 'descend'
8
9// Save sort preference to temporary state:
10await sortPreference.setValue({
11  column: table1.sortColumn,
12  direction: table1.sortDirection
13});
14// Note: setValue() is async — do not read sortPreference immediately after