CODEWITHSUNDEEP
sqlsqlite
nemo
nemo

Reputation: 35

Is it possible to select multiple offsets with SQL

I'd like to select multiple offsets with a single SELECT SQL query. Is this possible?

For example, let's say I have a table of non-continuous dates called dates.

date
----------
2020-01-01
2020-01-09
2020-01-16
2020-01-22
2020-01-29
2020-02-01
2020-02-04
2020-02-10
2020-02-13

I'd like to select the 1st, 4th and 7th offsets from the most recent date. In this example, the query should return:

offset    date
------    -------
1         2020-02-10
4         2020-01-29
7         2020-01-09

Ideally, I would like to write the query as follows:

SELECT
    *
FROM
    dates
ORDER BY
    date DESC
OFFSET
    (1, 4, 7)

Obviously, this does not compile due to the LIMIT/OFFSET clause.

I could try a UNION, but not only is this extremely verbose it does not work because the LIMIT/OFFSET clause must come after the last UNION.

FAIL:

SELECT 1, date FROM dates ORDER BY date DESC LIMIT 1 OFFSET 1
UNION
SELECT 4, date FROM dates ORDER BY date DESC LIMIT 1 OFFSET 4
UNION
SELECT 7, date FROM dates ORDER BY date DESC LIMIT 1 OFFSET 7

Is there an elegant query to do this? The other option I can think of is to use row_number() with a window function, and then do something like SELECT * WHERE row_number IN (1, 4, 7). HOWEVER, in some cases I am forced to use an older version of SQLite that does not support window functions.

Upvotes: 2

Views: 975

Answers (2)

forpas
forpas

Reputation: 164139

For earlier version of SQLite you can use a correlated query in the WHERE clause:

select t.date
from tablename t
where (select count(*) from tablename where date > t.date) in (1, 4, 7)

See the demo.
Results:

| date       |
| ---------- |
| 2020-01-09 |
| 2020-01-29 |
| 2020-02-10 |

If you also want the offset column:

select
  (select count(*) from tablename where date > t.date) offset,
  t.date
from tablename t
where offset in (1, 4, 7)
order by offset

See the demo.
Results:

| offset | date       |
| ------ | ---------- |
| 1      | 2020-02-10 |
| 4      | 2020-01-29 |
| 7      | 2020-01-09 |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270513

I think you want row_number():

select t.*
from (select t.*,
             row_number() over (order by date desc) as offset
      from t
      where date < date('now')  -- you might want local time
     ) t
where offset in (1, 4, 7);

Upvotes: 1

Related Questions