How to sort query results using random() order but stable, e.g. based on the day of the week?

Question

I have a query such as this:

SELECT
 cs1.id,
 cs1.uid,
 cs1.title,
 cs1.description
FROM related_chat_session rcs1
INNER JOIN chat_session cs1
ON cs1.id = rcs1.related_chat_session_id
WHERE
 rcs1.chat_session_id = 1ドル AND
 cs1.discoverable = true
ORDER BY random()
LIMIT 10

At the moment, it will return result in random order every time. However, I want to adjust the logic such that randomness would be stable based on a seed, where seed is (as an example) the day of the week.

i.e. The outcome would be that Monday results are listed in one order, Tuesday in another, etc.

How do I do this?

• Lilou Artz: The goal is to test which related questions get highest CTR. However, I don't want to rotate them every time user refreshes the page because that's annoying user experience (you notice something, then try to do find it again, and it is no longer there).

Answer 1

Try this ORDER BY clause:

ORDER BY hashint8extended(
 cs1.id::bigint,
 extract(dow FROM current_timestamp)::bigint
 )

Hash functions return deterministic, bu seemingly random values.

• Lilou Artz: This is perfect!