CODEWITHSUNDEEP
sqlpostgresqlpostgres15
codewise69420
codewise69420

Reputation: 33

How to have a row always on top even when using OFFSET?

I'm using Postgres.

I want to have a row always on top (example alias = 'abc') in a query regardless of the offset being used.

Right now, I'm doing

ORDER BY
CASE
    WHEN alias = 'abc' THEN 0
    ELSE 1
END
, avg_xyz
    DESC            
LIMIT 11 OFFSET 10;

but it doesn't work because of OFFSET. Works if OFFSET is 0.

Upvotes: 0

Views: 36

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247445

Use two queries:

(SELECT ...
 FROM tab
 WHERE ...
   AND alias = 'abc'
 ORDER BY avg_xyz DESC
 LIMIT 11)
UNION ALL
(SELECT ...
 FROM tab
 WHERE ...
   AND alias IS DISTINCT FROM 'abc'
 ORDER BY avg_xyz DESC
 OFFSET 10 LIMIT 11)
LIMIT 11

Upvotes: 2

Related Questions