Standard SQL and previous and next row values

Question

I know this is a common question and I've done some reading on it. What I want is a performant way (best in one query) for receiving the the next and previous row id, based on a reference row id. I've found many questions and answers here at stackoverflow and one valuable thread with a really nice answer https://stackoverflow.com/a/15992856/1230358. What I have is based on the answers in this thread.

select id from test_1
where ( 
    id = IFNULL((select max(id) from test_1 where id < 2 order by starts_on, id), 0)
    or id = IFNULL((select min(id) from test_1 where id > 2 order by starts_on, id), 0) 
)

Querying with the reference id=2 returns exactlythe result that I need (first row is the previous id, second row is the next id):

id
--
1
--
3

The problem is, if querying the edge cases id=1 or id=max(id), the results miss either the previous or next row id, because there simply is no no previous or next row. The result has now only one row and it's not clear if this is either the previous our next row id.

id
--
2       (next value)

However, i need a result like this

id
--
NULL    (or 0 - previous value)
--
2       (next value)

What I need is a solution based or similar in performance to the upper query, that fills the non-existant edgecases ids preferable with a NULL value (or 0). As I'm cunsuming the results with a webframework supporting different dbms, it should work with mysql, sqlite, and postgres. It should work with the following schema:

drop table if exists test_1;
create table test_1 (id INTEGER PRIMARY KEY,starts_on DATETIME, ends_on DATETIME);
insert into test_1 (starts_on, ends_on) Values ('2017-01-01 00:00:00', '2017-01-01 00:00:00');
insert into test_1 (starts_on, ends_on) Values ('2017-01-01 00:00:00', '2017-01-01 00:00:00');
insert into test_1 (starts_on, ends_on) Values ('2017-01-01 00:00:00', '2017-01-01 00:00:00');
insert into test_1 (starts_on, ends_on) Values ('2017-01-01 00:00:00', '2017-01-01 00:00:00');
insert into test_1 (starts_on, ends_on) Values ('2017-01-01 00:00:00', '2017-01-01 00:00:00');
insert into test_1 (starts_on, ends_on) Values ('2017-01-01 00:00:00', '2017-01-01 00:00:00');
insert into test_1 (starts_on, ends_on) Values ('2017-01-01 00:00:00', '2017-01-01 00:00:00');

drop table if exists test_2;
create table test_2 (id INTEGER PRIMARY KEY,starts_on DATETIME, ends_on DATETIME);
insert into test_2 (starts_on, ends_on) Values ('2017-01-01 00:00:00', '2017-01-07 00:00:00');
insert into test_2 (starts_on, ends_on) Values ('2017-01-02 00:00:00', '2017-01-08 00:00:00');
insert into test_2 (starts_on, ends_on) Values ('2017-01-03 00:00:00', '2017-01-09 00:00:00');
insert into test_2 (starts_on, ends_on) Values ('2017-01-04 00:00:00', '2017-01-10 00:00:00');
insert into test_2 (starts_on, ends_on) Values ('2017-01-05 00:00:00', '2017-01-11 00:00:00');
insert into test_2 (starts_on, ends_on) Values ('2017-01-06 00:00:00', '2017-01-12 00:00:00');
insert into test_2 (starts_on, ends_on) Values ('2017-01-07 00:00:00', '2017-01-13 00:00:00');


drop table if exists test_3;
create table test_3 (id INTEGER PRIMARY KEY,starts_on DATETIME, ends_on DATETIME);
insert into test_3 (starts_on, ends_on) Values ('2017-01-01 00:00:00', '2017-01-07 00:00:00');
insert into test_3 (starts_on, ends_on) Values ('2017-01-02 00:00:00', '2017-01-08 00:00:00');
insert into test_3 (starts_on, ends_on) Values ('2017-01-02 00:00:00', '2017-01-09 00:00:00');
insert into test_3 (starts_on, ends_on) Values ('2017-01-04 00:00:00', '2017-01-10 00:00:00');
insert into test_3 (starts_on, ends_on) Values ('2017-01-05 00:00:00', '2017-01-11 00:00:00');
insert into test_3 (starts_on, ends_on) Values ('2017-01-07 00:00:00', '2017-01-12 00:00:00');
insert into test_3 (starts_on, ends_on) Values ('2017-01-07 00:00:00', '2017-01-13 00:00:00');

Update:

A possible solution would be:

select distinct 
(select max(id) from test_1 where id < 7 order by starts_on, id) as prev, 
(select min(id) from test_1 where id > 7 order by starts_on, id) as next 
from test_1

Sorry forgott to mention that. Should be mysql, sqlite, and postgres. — hetsch, Feb 03 '17 at 11:45
The window functions *are* standard SQL, but not from the same version of the standard that SQLite is using. — CL., Feb 03 '17 at 11:53
Got it @CL. With standard, I've thought that I'm expressing that it works on every major db platform. My bad — hetsch, Feb 03 '17 at 11:57

score 1 · Answer 1 · answered Feb 03 '17 at 11:35

1

Postgresql Window Functions

select
    lag(id) over (order by starts_on) as previous,
    lead(id) over (order by starts_on) as next
from test_1
where id = 2

answered Feb 03 '17 at 11:35

Clodoaldo Neto

118,695
26
233
260

Allright, the problem is that I have to work with a webframework and it has to be standard sql supporting mysql, sqlite and postgres. As far as I know, sqlite3 does not support window functions. Sorry that it didn't came out of my question text. I will add that information. Thank you for this solution! – hetsch Feb 03 '17 at 11:42
@hetsch this answer **is** "standard SQL" – Feb 03 '17 at 12:49
@a_horse_with_no_name Yes, my bad. I've misunderstood what "standard" means. Naively I thought that it means the least common denominator that works across many dbms. – hetsch Feb 03 '17 at 13:02
@hetsch: that query does work across "many" DBMS. It's only MySQL (and SQLite to some extent) that doesn't support [modern SQL](http://modern-sql.com/). Even MariaDB has window functions and common table expressions nowadays – Feb 03 '17 at 13:03
@a_horse_with_no_name. After playing around with this solution, it doesn't seem to work for me in postgres. I would me more than thankful if you could have a quick look at this small gist http://rextester.com/QUHB68625. Thank's! – hetsch Feb 04 '17 at 12:20

CL. · Accepted Answer · 2017-02-03T11:55:25.497

0

In the general case, to combine the results of two queries, you can use them as subqueries, and either put them into two columns (as scalar subqueries):

SELECT (SELECT ...) AS a, (SELECT ...) AS b;

or into two rows:

SELECT * FROM (SELECT ...
               UNION ALL
               SELECT NULL
               LIMIT 1)
UNION ALL
SELECT * FROM (SELECT ...
               UNION ALL
               SELECT NULL
               LIMIT 1);

(The SELECT NULL LIMIT 1 construction ensures that a NULL is returned if the actual query does not return a row.)

edited Feb 03 '17 at 11:55

answered Feb 03 '17 at 11:50

CL.

173,858
17
217
259

Perfect! So in my case, this seems to work: `select distinct IFNULL((select max(id) from test_1 where id < 7 order by starts_on, id), 0) as prev, IFNULL((select min(id) from test_1 where id > 7 order by starts_on, id), 0) as next from test_1` Do you see any performance issues on large datasets with this query? Thank's – hetsch Feb 03 '17 at 12:01
These are likely to be easier to optimize than a single query with OR, but how fast it is in practice is something you have to measure yourself. – CL. Feb 03 '17 at 12:59

score 0 · Answer 3 · answered Feb 03 '17 at 11:52

Postgres, like many more modern databases, as opposed to MySQL, supports Analytic, Window functions, also called OLAP functions.

What you want here is a combination of the analytic LEAD() and LAG() functions. You will need to combine them with with the COALESCE() function, as PostGres, to my knowledge, does not support NVL() or IFNULL(), if you want something else than NULL. If you work on the starts_on and ends_on dates, here's an example with starts_on.

SELECT
  COALESCE(LAG(starts_on) OVER (ORDER BY starts_on),'1900-01-01 00:00:00')
  AS neighbour_starts_on
FROM test_1
WHERE starts_on = '2017-01-07 00:00:00'
UNION ALL SELECT
  COALESCE(LEAD(starts_on) OVER (ORDER BY starts_on),'9999-12-31 23:59:59')
  AS neighbour_starts_on
FROM test_1
WHERE starts_on = '2017-01-07 00:00:00'

Standard SQL and previous and next row values

3 Answers3