Grouped Sorting

We wish to sort in a grouping context. There are two common scenarios that we cover here:

  1. Sorting Groups where we sort the rows of a table in order of a property of the group they belong to, e.g. group size, where the groups are defined by another column of the table.
  2. Sorting Within Groups where we sort a grouped table such that sorting happens within groups.

Sorting Groups

We wish to sort the rows of a table in order of a property of the group they belong to where the groups are defined by another column of the table.

In this example, we wish to sort the rows of a table in descending order of the size of the group they belong to where the groups are defined by the column col_6.

WITH groups_table AS
         (SELECT col_6, COUNT(1) group_size
          FROM refcon.examples.dummy_table
          GROUP BY col_6)

SELECT t.*
FROM refcon.examples.dummy_table t
         LEFT JOIN groups_table g ON g.col_6 = t.col_6

ORDER BY g.group_size DESC;

Here is how this works:

  • We first calculate the group size in a CTE by grouping by col_6. See Grouped Transformation.
  • We join groups_table with dummy_table using col_6.
  • We use ORDER BY to sort the table in descending order (hence DESC) of the value of group_size.
  • The resulting table will be sorted in descending order of the size of the group each row belongs to.
  • We select t.* to only keep the columns belonging to dummy_table.

Sorting Within Groups

We wish to sort a grouped table such that sorting happens within groups.

In this example, we wish to group the table by the column col_6 and then sort each group by the values of the column col_2 in ascending order.

SELECT col_6, COUNT(1) group_size
FROM refcon.examples.dummy_table
GROUP BY 1
ORDER BY group_size

Here is how this works:

  • We use GROUP BY to group the table by the values of the column col_6.
  • We sort by group_size using ORDER BY clause.
SQL
I/O