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
.
Best Answer
This returns the top 3 rows according to
ORDER BY x DESC
and appends the rest sorted byy DESC
:TABLE
is just shorthand forSELECT * 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 enclosedSELECT
, not to the complete set of rows.See:
Combine multiple SELECT statements
Is order preserved after UNION in PostgreSQL?
Related:
Combining 2 SELECT queries and printing the results in PostgreSQL
Is there a faster way to get UNION ALL behavior in Postgres?
Without
UNION
Same result.
The
LEFT JOIN
results inc.x
beingNULL
except for the top 3 chosen rows. So the firstORDER BY
itemsc.x DESC NULLS LAST
only sorts the first 3 rows and leaves the rest unsorted. The secondORDER BY
iteme.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)