Example: SQLite, dates and years

[bule]

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)

[RizlaUK]

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

[bule]

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

[Intrigued]

More useful tips, thanks guys.