How to make an app - Andromo App Maker


No announcement yet.

Example: SQLite, dates and years

  • Filter
  • Time
  • Show
Clear All
new posts

  • Example: SQLite, dates and years

    Here is a little neat thing I've just realised:

    For example, you have a table 'mytable' with columns 'name' and 'date':
    - 'name' is a string
    - 'date' is an integer, in a form YYYYMMDD

    So, you have a table filled with a lot of records for many years.

    What if you want to have a ComboBox with Year selection, that would
    only show the years for the dates that are already in the database?
    Selecting all rows using 'DISTINCT date' and then parsing using Lua would
    work, but it would not be that optimised since many rows would be returned.
    Instead, you could try this:

    SELECT DISTINCT substr(date,1,4) FROM mytable;

    This will only return one row per year! Now, is this super-neat or what?

    BTW, the table index for year column in this case would be:
    (assuming you would use 'mytable' as a return value, and 'count' as a for iterator)
    Never know what life is gonna throw at you. ZubTech

  • #2
    yeah thats pretty neat,

    i have always used "combo.find" and compared the results to the current row in the loop and if no match then add the item, it makes sense to do it all within the sqlite santax,

    Thanks :yes
    Embrace change in your life, you never know, it could all work out for the best


    • #3
      You could do the query like this as well:

      SELECT DISTINCT substr(date,1,4) AS year FROM mytable;

      Then, you could reference to it like this:
      Never know what life is gonna throw at you. ZubTech


      • #4
        More useful tips, thanks guys.