How to select the oldest group of dates from sqlite

Question

I'm creating a new database for a customer, and want to select the oldest group of dates based on a variable that he enters through a tkinter gui. I'm using Sqlite and I can't seem to find an answer since the date is stored as text. Is there a way of comparing dates and selecting the oldest few based on a variable. I don't need to select 2 dates and the products in between I want to select 3 products to go out and the program automatically selects the oldest 3 products in the database Thank you. :)

For example If there are 5 products in the table:

1. • product one on january 1st.
2. • product two on january 10th.
3. • product three is january 3rd.
4. • product 4 is february 5th.
5. • product 5 is february 2nd.

I'd like to select the oldest 4

So the result would be:

1. • the row that contains product 1
2. • the row that contains product 3
3. • the row that contains product 2
4. • the row that contains product 5.

How can I do that?

Answer 1

You can use ORDER BY on the field you want to sort by and LIMIT the results:

SELECT * FROM orders ORDER BY order_date ASC LIMIT 5

This would select the lowest 5 orders according to the order date. You can change the direction of sorting by using DESC.

Answer 2

I believe you would use something like (assuming that the date column is named product_date)

SELECT * FROM products ORDER BY product_date ASC LIMIT 4;

• The date being in YYYY-MM-DD format will be sorted textually but that would be fine.

e.g.

INSERT INTO products 
    VALUES
        ('Product 1','20190101'),
        ('Product2','20190110'),
        ('Product3','20190103'),
        ('Product4','20190205'),
        ('Product5','20190204');
SELECT * FROM products ORDER BY product_date ASC LIMIT 4;

Results in :-

Considering the comment

what if I want to make the limit a variable

This is an example where the LIMIT is passed as a variable (limit_var) that is bound (i.e. replaces the ?) :-

def so58207795():
    import sqlite3

    db = sqlite3.Connection("mycart")
    db.execute("CREATE TABLE IF NOT EXISTS products(product TEXT, product_date TEXT)")
    db.execute("INSERT INTO products VALUES('Product 1','20190101'),('Product2','20190110'),('Product3','20190103'),('Product4','20190205'),('Product5','20190204')")
    limit_var = "4"
    print(db.execute("SELECT * FROM products ORDER BY product_date ASC LIMIT ?",(limit_var)).fetchall())

Resulting in :-

E:\PYCharmPythonProjects\venv\Scripts\python.exe E:/PYCharmPythonProjects/Test001.py
[('Product 1', '20190101'), ('Product3', '20190103'), ('Product2', '20190110'), ('Product5', '20190204')]

Process finished with exit code 0