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.
Rank a few items at the top or bottom of a database query.
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
In both cases the idea is to inject a value for certain records, so that it can be used in the `ORDER BY` clause.
A simple solution based on
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.
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
attribute makes it very simple to inspect the result at various levels inside of
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.