Andromo - Start Making Apps - Free Signup

Announcement

Collapse

New Forum Software

If you're here, you've found the new home for our forums. There will be some bugs to iron out, so thanks for your patience...
See more
See less

Example: SQLite, dates and years

Collapse
X
  • 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:
    mytable.Data[count]["substr(date,1,4)"]
    (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

    Comment


    • #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:
      mytable.Data[count].year
      Never know what life is gonna throw at you. ZubTech

      Comment


      • #4
        More useful tips, thanks guys.
        Intrigued

        Comment

        Working...
        X