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
.
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:
Combining 2 SELECT queries and printing the results in PostgreSQL
Is there a faster way to get UNION ALL behavior in Postgres?
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
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP