Is it possible to select multiple offsets with SQL2020-02-14 sql sqlite
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
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.
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.
I think you want
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);
For earlier version of SQLite you can use a correlated query in the
select t.date from tablename t where (select count(*) from tablename where date > t.date) in (1, 4, 7)
See the demo.
| date | | ---------- | | 2020-01-09 | | 2020-01-29 | | 2020-02-10 |
If you also want the
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.
| offset | date | | ------ | ---------- | | 1 | 2020-02-10 | | 4 | 2020-01-29 | | 7 | 2020-01-09 |
- Add a column with a default value to an existing table in SQL Server
- How do I UPDATE from a SELECT in SQL Server?
- How can I prevent SQL injection in PHP?
- How do I perform an IF...THEN in an SQL SELECT?
- Finding duplicate values in a SQL table
- How do I import an SQL file using the command line in MySQL?
- How to concatenate text from multiple rows into a single text string in SQL server?
- Select first row in each GROUP BY group?
- Improve INSERT-per-second performance of SQLite?