TransWikia.com

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

Database Administrators Asked by Oğuz Can Sertel on December 19, 2021

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.

2 Answers

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:

Related:

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:

db<>fiddle here

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

Answered by Erwin Brandstetter on December 19, 2021

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;

Answered by Boothy on December 19, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP