CODEWITHSUNDEEP
sqlpostgresql
Marcus Junius Brutus
Marcus Junius Brutus

Reputation: 27276

select the row that's "before" a given row in some ordering

What's the idiomatic way to select a row that's identified as the one that's coming up before a row we are given?

An example to make this clear:

CREATE TABLE entry (x VARCHAR, i INTEGER);
ALTER TABLE entry ADD PRIMARY KEY (x, i);
INSERT INTO entry (x,i) VALUES ('a', 1);
INSERT INTO entry (x,i) VALUES ('a', 2);
INSERT INTO entry (x,i) VALUES ('b', 1);

Table 'entry' has a clear lexicographical ordering according to the natural ordering:

SELECT * FROM entry ORDER BY x, i

If I am given the b and 1 (i.e. the ('b', 1) row) how do I write a query that selects the row that's coming up before that? (i.e. the ('a', 2) row). The query should return an empty row set if given the "first" row (in the case above, the ('a', 1) row).

Upvotes: 1

Views: 49

Answers (2)

Randy
Randy

Reputation: 16677

Use a LAG Window function. (also LEAD when appropriate)

http://www.postgresql.org/docs/8.4/static/functions-window.html

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can do this with order by and limit and a where clause:

select e.*
from entry e
where x < 'b' or x = 'b' and i < 1
order by x desc, b desc
limit 1;

Upvotes: 1

Related Questions