Skip to content

Ordering

Usually, queries are ordered before LIMIT and OFFSET are applied. Beam supports the standard SQL ORDER BY construct through the orderBy_ function.

orderBy_ works like the Haskell function sortBy, with some restrictions. Its first argument is a function which takes as input the output of the given query. The function should return a sorting key, which is either a single sort ordering or a tuple of them. A sort ordering specifies an expression and a direction by which to sort. The result is then sorted lexicographically based on these sort expressions. The second argument to orderBy_ is the query whose results to sort.

Use the asc_ and desc_ functions to specify the sort ordering over an arbitrary expression.

Note

Use nullsFirst_ and nullsLast_ to control the ordering of nulls. See advanced features for more information.

For example, to get the first ten albums when sorted lexicographically, use

limit_ 10  $
orderBy_ (asc_ . albumTitle) $
all_ (album chinookDb)

SELECT "t0"."AlbumId" AS "res0",
       "t0"."Title" AS "res1",
       "t0"."ArtistId" AS "res2"
FROM "Album" AS "t0"
ORDER BY "t0"."Title" ASC
LIMIT 10

SELECT "t0"."AlbumId" AS "res0",
       "t0"."Title" AS "res1",
       "t0"."ArtistId" AS "res2"
FROM "Album" AS "t0"
ORDER BY "t0"."Title" ASC
LIMIT 10;

-- With values: []

Again, note that the ordering in which you apply the limit_ and orderBy_ matters. In general, you want to sort before you limit or offset, to keep your result set stable. However, if you really want to sort a limited number of arbitrarily chosen rows, you can use a different ordering.

orderBy_ (asc_ . albumTitle) $
limit_ 10 $
all_ (album chinookDb)

SELECT "t0"."res0" AS "res0",
       "t0"."res1" AS "res1",
       "t0"."res2" AS "res2"
FROM
  (SELECT "t0"."AlbumId" AS "res0",
          "t0"."Title" AS "res1",
          "t0"."ArtistId" AS "res2"
   FROM "Album" AS "t0"
   LIMIT 10) AS "t0"
ORDER BY "t0"."res1" ASC

SELECT "t0"."res0" AS "res0",
       "t0"."res1" AS "res1",
       "t0"."res2" AS "res2"
FROM
  (SELECT "t0"."AlbumId" AS "res0",
          "t0"."Title" AS "res1",
          "t0"."ArtistId" AS "res2"
   FROM "Album" AS "t0"
   LIMIT 10) AS "t0"
ORDER BY "t0"."res1" ASC;

-- With values: []

Multiple ordering keys

You can specify multiple keys to order by as well. Keys are sorted lexicographically in the given direction, as specified in the SQL standard.

For example, we can sort all employees by their state of residence in ascending order and by their city name in descending order.

limit_ 10 $
orderBy_ (\e -> (asc_ (addressState (employeeAddress e)), desc_ (addressCity (employeeAddress e)))) $
all_ (employee chinookDb)

SELECT "t0"."EmployeeId" AS "res0",
       "t0"."LastName" AS "res1",
       "t0"."FirstName" AS "res2",
       "t0"."Title" AS "res3",
       "t0"."ReportsTo" AS "res4",
       "t0"."BirthDate" AS "res5",
       "t0"."HireDate" AS "res6",
       "t0"."Address" AS "res7",
       "t0"."City" AS "res8",
       "t0"."State" AS "res9",
       "t0"."Country" AS "res10",
       "t0"."PostalCode" AS "res11",
       "t0"."Phone" AS "res12",
       "t0"."Fax" AS "res13",
       "t0"."Email" AS "res14"
FROM "Employee" AS "t0"
ORDER BY "t0"."State" ASC,
         "t0"."City" DESC
LIMIT 10

SELECT "t0"."EmployeeId" AS "res0",
       "t0"."LastName" AS "res1",
       "t0"."FirstName" AS "res2",
       "t0"."Title" AS "res3",
       "t0"."ReportsTo" AS "res4",
       "t0"."BirthDate" AS "res5",
       "t0"."HireDate" AS "res6",
       "t0"."Address" AS "res7",
       "t0"."City" AS "res8",
       "t0"."State" AS "res9",
       "t0"."Country" AS "res10",
       "t0"."PostalCode" AS "res11",
       "t0"."Phone" AS "res12",
       "t0"."Fax" AS "res13",
       "t0"."Email" AS "res14"
FROM "Employee" AS "t0"
ORDER BY "t0"."State" ASC,
         "t0"."City" DESC
LIMIT 10;

-- With values: []