PostgreSQL - Slice the bottom N results and move them to the top

Question

Let's say, for example, I have a list of email addresses retrieved like so:

SELECT email FROM users ORDER BY email

This returns:

[email protected]
[email protected]
[email protected]
...
[email protected]
[email protected]
[email protected]

I'd like to take this result set, slice the bottom 3 emails and move them to the top, so you'd see a result set like:

[email protected]
[email protected]
[email protected] -- Note x-z is here
[email protected]
[email protected]
[email protected]
...
[email protected]
[email protected]
[email protected]

Is there a way to do this within the SQL? I'd like to not have to do it application-side for memory reasons.

Answer 1

If you know the values are "x" or greater, you can simply do:

order by (case when email >= '[email protected]' then 1 else 2 end),
         email

Otherwise, you can use row_number():

select email
from (select email, row_number() over (order by email desc) as seqnum
      from users u
     ) u
order by (case when seqnum <= 3 then 1 else 2 end),
         email;

Answer 2

Here's one version with window functions row_number() and count():

SELECT email
  FROM (
    SELECT row_number() OVER (ORDER BY email), count(*) OVER (), email
      FROM users
  ) sq1
  ORDER BY count - 3 >= row_number, email;

Answer 3

Assuming email is defined UNIQUE NOT NULL. Else you need to do more.

SELECT email
FROM  (SELECT email, row_number() OVER (ORDER BY email DESC) AS rn FROM users) sub
ORDER  BY (rn > 3), rn DESC;

In Postgres you can just sort by a boolean expression. FALSE sorts before TRUE. More:

• Sorting null values after all others, except special

Secondary, sort by the computed row number (rn) in descending order. Don't sort by the (more expensive) text column email another time. Shorter and simpler - test with EXPLAIN ANALYZE, it should be faster, too.