geisterfurz007 geisterfurz007 - 1 month ago 9
SQL Question

Nested Select statement - Getting datasets with strings between two string values

I am currently trying to fetch entries from a database with a certain range. The strings found in the table are dates in the format

yyyyMMdd
. Edit: stored as strings; not as dates!

The table looks like this (without ID):

+----------+----------+----------+
| c1 | c2 | c3 |
+----------+----------+----------+
| 20161101 | 20161102 | 20161027 |
| 20131212 | 20161102 | 20161028 |
+----------+----------+----------+


With the following code I can get the smallest "value" over the three columns for each row:

SELECT MIN(c1, c2, c3) FROM my_table WHERE c1 IS NOT NULL AND c2 IS NOT NULL AND c3 IS NOT NULL;

+-----------------+
| MIN(c1, c2, c3) |
+-----------------+
| 20161027 |
| 20131212 |
+-----------------+


However when I try to use it as part of a nested SQL-Query to get only the ones that are e.g. made between 2015 and 2017 (excluding the second row) with this query:

SELECT * FROM my_table WHERE MIN(c1, c2, c3) IN (SELECT MIN(c1, c2, c3) FROM my_table WHERE c1 IS NOT NULL AND c2 IS NOT NULL AND c3 IS NOT NULL) BETWEEN '2015' AND '2017';


I do not get any results at all. I tested other things as well like
... LIKE '2016%'
(not producing any results) and
... LIKE '%'
Giving all results shown above in the table.

What I want to get out would be

+----------+----------+----------+
| c1 | c2 | c3 |
+----------+----------+----------+
| 20161101 | 20161102 | 20161027 |
+----------+----------+----------+


I assume that I am working incorrectly with the nested SELECT-statement, but after 2 hours of searching and testing I cannot figure out what is wrong.

I am using SQLite Studio 3.1.0 to test the queries and a local sqlite 3 database to execute the queries on.

Any help is much appreciated! If I should add more possible inputs/outputs, tell me so I can do so.

Answer

Your logic appears to be that you want to retain records where the minimum date of (c1, c2, and c3) is between 2015 and 2017. If so, then the following should work:

SELECT *
FROM my_table
WHERE STRFTIME('%Y-%m-%d', MIN(c1, c2, c3)) BETWEEN DATE('2015-01-01') AND DATE('2017-12-31')

Further you should change the format to YYYY-mm-dd.