PostgreSQL - order randomly, but with NULLs first

Question

I have a query that takes all rows out of a table, and joins with another table I am updating. The other table has some items that have been checked (these get a value), and some which are not yet checked. I am trying to implement a way to update all records, but make sure any NULLs get sorted as quickly as possible. I have the following query:

SELECT * FROM posts
LEFT JOIN post_stats
ON post_stats.post_id = posts.id
ORDER BY RANDOM() NULLS FIRST LIMIT 10

However, this is ordering everything randomly. Is there a way to order everything randomly, but any NULLs get shown first?

random() will never return a NULL value, so ORDER BY RANDOM() NULLS FIRST doesn't really make sense. — user330315
– user330315, Commented Jan 12, 2017 at 11:10

Fabian Pijcke · Accepted Answer · 2017-01-12 17:21:03Z

1

Note that you don't even specify which column can contain NULLs in your query. This is an indicator that something is going wrong.

The following query (replace with what you need) should do what you want.

SELECT *
FROM posts
LEFT JOIN post_stats ON post_stats.post_id = posts.id
ORDER BY <YOUR_COLUMN> IS NOT NULL, RANDOM()
LIMIT 10;

answered Jan 12, 2017 at 17:21

Fabian Pijcke

3,31027 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL - order randomly, but with NULLs first

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related