Nearly every software shows some type of items in an ordered way. In the context of web applications I often faced the challenge to bump certain items to the top or the bottom of this result. In this post I am looking into simple options to inject this custom position for an existing SQL query based on PostgreSQL.

Goal

Rank a few items at the top or bottom of a database query.

Overview of involved bits and pieces

• id IN (1, 2, 3) would allow for a simple check, even for multiple IDs.
• array_position() is a function which returns the position of a value inside of an array or NULL.

In both cases the idea is to inject a value for certain records, so that it can be used in the `ORDER BY` clause.

Using IN and CASE

A simple solution based on CASE:

SELECT
  id,
  CASE
    WHEN id IN (1, 2) THEN 1
    ELSE 9999
  END AS position
 FROM item
ORDER BY position ASC, id DESC;

The result looks in my test dataset like this:

 id  | position
-----|----------
   2 |        1
   1 |        1
 119 |     9999
 118 |     9999
 117 |     9999
 116 |     9999

This is already a quite nice tool. Certain items can be bumped to the top of the result set. It does not apply any logic to influence the order of the bumped items.

This might be desired if a few items shall be at the top, but then still sorted based on the original ORDER BY clause.

Using array_position

Using array_position we can already achieve a more sophisticated result, since the position inside the array can be used for sorting:

SELECT
  id,
  array_position(ARRAY[10, 5, 1], id) AS position
  FROM item
 ORDER BY
   position ASC NULLS LAST,
   id DESC

Compared to the first approach we see now that position will get an individual value for record in the result set. This allows to ensure that the first records are exactly in the order as specified in the array of IDs:

 id  | position
-----|----------
  10 |        1
   5 |        2
   1 |        3
 119 |
 118 |
 117 |

It is also possible to directly embed the expression into the ORDER BY clause:

SELECT
  id
  FROM item
 ORDER BY
   array_position(ARRAY[10, 5, 1], id) ASC NULLS LAST,
   id DESC

My personal preference is to keep the value in the result set since this helps to understand the order just based on the returned data. Often the data is further processed inside of an application. So that keeping the position attribute makes it very simple to inspect the result at various levels inside of the application.

Conclusions

For my case using array_position seems to be the nicest approach, since it allows to rank a few elements at the top of a result set. This can be used in anything which is like a search result on a per request basis to keep a few elements at the top of the list.

Depending on how a system looks like, this approach might be totally off, e.g. a system which does a search on a dedicated search infrastructure or applies certain caching strategies of the result documents should most probably be approached in a different way. If a simple SQL query shall be tweaked, the options above seem to be a nice solution though.