How to order some rows one way, and the rest another way?

Question

I am trying to sort rows from a table in PostgreSQL 10.12:
CREATE TABLE example (
 id  serial PRIMARY KEY,
 val text NOT NULL,
 x   integer NOT NULL,
 y   integer NOT NULL
);
    
INSERT INTO example (val,x,y)
VALUES
  ('First Value (This should be first order)',7,2)
, ('Second Value (This should be second order)',6,3)
, ('Third Value (This should be third order)',5,4)
, ('Seventh Value (And This should be last order)',4,1)
, ('Sixth Value (This should be sixth order)',3,5)
, ('Fifth Value (This should be fifth order)',2,6)
, ('Fourth Value (This should be fourth order)',1,7)
;

First three result rows should be ordered by x desc, and the rest should be ordered by y desc.
I tried this query but it is ordering only by y:
SELECT * from (SELECT * from example order by x desc fetch first 3 rows only) foo
UNION
SELECT * from example order by y desc;

But it sorts by only y. And I need to get the result without UNION.
Here is an SqlFiddle.

Erwin Brandstetter · Answer

This returns the top 3 rows according to ORDER BY x DESC and appends the rest sorted by y DESC:
WITH cte AS (TABLE example ORDER BY x DESC LIMIT 3)
TABLE cte
UNION ALL
(  -- parentheses required
SELECT e.*
FROM   example e
LEFT   JOIN cte USING (id)
WHERE  cte.id IS NULL
ORDER  BY e.y DESC
);

TABLE is just shorthand for SELECT * FROM.
UNION ALL makes sure that the two derived tables are just appended without trying to eliminate (non-existent) duplicates and thereby messing with the sort order.
Parentheses are required so that ORDER BY only applies enclosed SELECT, not to the complete set of rows.
See:

Combine multiple SELECT statements

Is order preserved after UNION in PostgreSQL?

Is there a faster way to get UNION ALL behavior in Postgres?

Without UNION
SELECT e.*
FROM   example  e
LEFT   JOIN (
   SELECT id, x FROM example ORDER BY x DESC LIMIT 3
   ) c USING (id)
ORDER  BY c.x DESC NULLS LAST, e.y DESC;

Same result.
The LEFT JOIN results in c.x being NULL except for the top 3 chosen rows. So the first ORDER BY items c.x DESC NULLS LAST only sorts the first 3 rows and leaves the rest unsorted. The second ORDER BY item e.y DESC sorts the rest as desired.
About NULLS LAST:

Sort by column ASC, but NULL values first?

db<>fiddle here
If the table is not trivially small, you should have and index on (x) and another one on (y)

Boothy · Answer

You could try a common table expression:
with    cte_example
            as
            (
                select row_number() over (order by x desc) as my_row_count
                , id, val, x, y
                from example
            )   
            select my_row_count, id, val, x, y
            from cte_example
            where my_row_count <= 3;

How to order some rows one way, and the rest another way?

2 Answers

Without `UNION`

Add your own answers!

Ask a Question