2

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.

1
  • @ErwinBrandstetter Thank you for the comment. Actually I couldn't explain the question well enough. I have updated it. I hope it is clear now. – Oğuz Can Sertel Jul 24 '20 at 0:28
1

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)

4
  • Thank you for the answer. Actually I think I couldn't explain well enough the question. So I updated the question – Oğuz Can Sertel Jul 24 '20 at 0:22
  • Can we get the same result without union ? – Oğuz Can Sertel Jul 24 '20 at 0:31
  • @OğuzCanSertel: Yes, I have a nice one. – Erwin Brandstetter Jul 24 '20 at 0:40
  • Both of are perfect and very educated answers for me. Thank you so much. – Oğuz Can Sertel Jul 24 '20 at 0:53
0

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;
1
  • Thank you for the answer. Actually I think I couldn't explain well enough the question. So I updated the question – Oğuz Can Sertel Jul 24 '20 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.